読者です 読者をやめる 読者になる 読者になる

tocsatoの備忘録

ほぼほぼ 50 代のプログラマの備忘録。swift golang javascript css html5 nginx mysql などを最近使ってます。

mysqlでタイムゾーンをキチンと扱ってみる

mysql

データベースのタイムゾーンを確認する

SHOW VARIABLES LIKE '%time_zone%';

ウチのmacではこんな設定になってました。

変数名 意味
system_time_zone JST データベースシステムのタイムゾーン
JST = 日本時間
time_zone SYSTEM セッションのデフォルトのタイムゾーン
SYSTEM = system_time_zone と同じ

変数 time_zoneが変わると何が変わる?

SET SESSION time_zone = "..."

を実行すると、セッション毎にタイムゾーンを変えることができます。
セッションのタイムゾーンを変えると、

  • NOW()やSYSDATE()が返す日付・時刻の表現は変わります
  • timestampに格納した値の表現は変わります
  • datetimeに格納した値の表現は変わりません

mysql の設計思想は、

ということだと思います。

timestamp型の制約

timestamp 型は便利ですが、datetime 型とは値の範囲に (1970-01-01 00:00:01 UTC 〜 2038-01-19 03:14:07 UTC) という制約があります。
もうすぐそこまで迫っている 2038 年。
この制約があると積極的に使えない大きな問題と言えます。

datetime で タイムゾーンを意識して日付時刻を扱う

timestampは UTC で保存し、読み出し時に time_zone に合わせて変換して返します。
これをエミュレートするなら、タイムゾーンを変換する関数 CONVERT_TZ() を使用して、次のような SQL で対応できます。

INSERT INTO T(`val_datetime`) VALUES
    (CONVERT_TZ("2016-08-01 13:00:00", @@time_zone, "UTC"));

SELECT
    CONVERT_TZ(`val_datetime`, "UTC", @@time_zone) AS `val_datetime`
FROM
    T;

タイムゾーンが利用できない

mysql> SET @@SESSION.time_zone = "Asia/Tokyo";
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Tokyo'

mysql> SELECT CONVERT_TZ('2016-01-01 12:00:00','UTC','Asia/Tokyo') AS X;
+------+
| X    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

といった症状がでることがあります。
これは mysqlタイムゾーン情報が登録されていない場合に見られます。
mysqlタイムゾーン情報を登録することで解決できます。
コマンドラインから、

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

を実行することで、mysql.time_zone などのシステムテーブルにタイムゾーン情報が登録されます。