MySQLのタイムゾーン変換で少しはまった件

EPOCH時間をタイムスタンプに変換

from_unixtime()関数で得られる。

mysql> select from_unixtime(1517136662);
+---------------------------+
| from_unixtime(1517136662) |
+---------------------------+
| 2018-01-28 19:51:02       |
+---------------------------+
1 row in set (0.02 sec)

ただし、この時返ってくるタイムスタンプのタイムゾーンはMySQLのtime_zoneの設定値に依存する。
上記の例はJSTの場合。

タイムゾーンの設定に依存せずに常にUTCで取得したかったので以下のようなSQLを書いたところnullが返ってきた。

mysql> convert_tz(from_unixtime(1517136662), @@session.time_zone, 'utc');
+-------------------------------------------------------------------+
| convert_tz(from_unixtime(1517136662), @@session.time_zone, 'utc') |
+-------------------------------------------------------------------+
| NULL                                                              |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

どうやらタイムゾーンテーブルなるものをロードしなければならないらしい。

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql> select convert_tz(from_unixtime(1517136662), @@session.time_zone, 'utc');
+-------------------------------------------------------------------+
| convert_tz(from_unixtime(1517136662), @@session.time_zone, 'utc') |
+-------------------------------------------------------------------+
| 2018-01-28 10:51:02                                               |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)