MySQLのタイムゾーン

YAPC::Asia 2015 のセッションで、MySQL のタイムゾーンの話が出ていましたが、以前タイムゾーン周りで少しはまったことがあったのを思い出したので書いてみます。

MySQLのデフォルトのタイムゾーンは mysqld 起動時のシステム設定です。TZ 環境変数の値か、変数が設定されていなければ /etc/localtime(Ubuntu の場合) です。

# TZ=Japan /usr/sbin/mysqld

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
mysql> system date; SELECT NOW();
2015年  8月 22日 土曜日 21:39:12 JST
+---------------------+
| now()               |
+---------------------+
| 2015-08-22 21:39:12 |
+---------------------+

# TZ=UTC /usr/sbin/mysqld

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
mysql> system date; SELECT NOW();
2015年  8月 22日 土曜日 21:39:52 JST
+---------------------+
| now()               |
+---------------------+
| 2015-08-22 12:39:52 |
+---------------------+

MySQL のサーバーとクライアントでタイムゾーンが合っていないと、何かまずいことが起きるのか見てみます。

mysql> CREATE TABLE test (ts TIMESTAMP, dt DATETIME);
mysql> INSERT INTO test (ts, dt) VALUES (NOW(), NOW());
mysql> INSERT INTO test (ts, dt) VALUES ('2015-08-22 21:56:39', '2015-08-22 21:56:39');
mysql> SELECT * FROM test;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2015-08-22 12:56:09 | 2015-08-22 12:56:09 |
| 2015-08-22 21:56:39 | 2015-08-22 21:56:39 |
+---------------------+---------------------+

NOW() はサーバー側で実行されるので、サーバー側の時刻になります。クライアントが自分のタイムゾーンの時刻を取得して値を設定するとクライアントの時刻になります。

ここで、タイムゾーンの設定が誤っていることに気づいて、mysqld を起動しなおしました。

# TZ=Japan /usr/sbin/mysqld

mysql> select * from test;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2015-08-22 21:56:09 | 2015-08-22 12:56:09 |
| 2015-08-23 06:56:39 | 2015-08-22 21:56:39 |
+---------------------+---------------------+

TIMESTAMP カラムに設定した値が変わってしまいました。

TIMESTAMP カラムは内部的には 1970-01-01 00:00:00 UTC からの経過秒数で値を保持していて、その値を現在のタイムゾーンに合わせて日時形式に変換しているためです。 UTC の 12:56:09 と JST の 21:56:09 は TIMESTAMP の内部表現的には同じ値になっています。

DATETIME カラムは指定した値がそのまま保持されているため、タイムゾーンの影響を受けません。

TIMESTAMP カラムに値を登録した後にタイムゾーンを変更するようなことは避けたほうがいいでしょう。 個人的には、時刻を保持するには DATETIME カラムにして、TIMESTAMP は使わないのをおすすめしたいです。

DATETIME カラムの場合でも、NOW() 等の時刻取得関数はタイムゾーンの影響を受けるので、サーバーとクライアントのタイムゾーンは合わせておいた方が無難です。

AWS の RDS のように、システム設定を変更したり、mysqld 起動時の設定をいじることができない場合は、接続後に time_zone 変数を設定することで動きを変更できます。

mysql> SELECT @@system_time_zone, @@global.time_zone, @@time_zone;
+--------------------+--------------------+-------------+
| @@system_time_zone | @@global.time_zone | @@time_zone |
+--------------------+--------------------+-------------+
| UTC                | SYSTEM             | SYSTEM      |
+--------------------+--------------------+-------------+
mysql> SET time_zone = '+09:00';
mysql> SELECT @@system_time_zone, @@global.time_zone, @@time_zone;
+--------------------+--------------------+-------------+
| @@system_time_zone | @@global.time_zone | @@time_zone |
+--------------------+--------------------+-------------+
| UTC                | SYSTEM             | +09:00      |
+--------------------+--------------------+-------------+
mysql> SET global time_zone = '+09:00';
mysql> SELECT @@system_time_zone, @@global.time_zone, @@time_zone;
+--------------------+--------------------+-------------+
| @@system_time_zone | @@global.time_zone | @@time_zone |
+--------------------+--------------------+-------------+
| UTC                | +09:00             | +09:00      |
+--------------------+--------------------+-------------+

system_time_zone は変更できません。time_zone は現在の接続のみで有効です。global.time_zone はすべての接続で有効になります。既に接続中のクライアントには影響しません。global.time_zone の設定は mysqld が終了するまで有効です。

日本の場合は夏時間が無く、UTC との時差は常に +09:00 なので上記のような設定でいいのですが、夏時間があり、UTC との時差が季節によって異なるような地域では固定の値の設定では問題になります。

デフォルト状態では Japan のようなシンボルを time_zone 変数に指定することはできません。

mysql> SET time_zone = 'Japan';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Japan'

実は MySQL はタイムゾーン用のシステムテーブルを持っています。ただし初期状態では中身は空っぽです。

mysql> USE mysql
mysql> SHOW TABLES LIKE '%time_zone%';
+-------------------------------+
| Tables_in_mysql (%time_zone%) |
+-------------------------------+
| time_zone                     |
| time_zone_leap_second         |
| time_zone_name                |
| time_zone_transition          |
| time_zone_transition_type     |
+-------------------------------+
mysql> SELECT * FROM time_zone;
Empty set (0.00 sec)

mysql_tzinfo_to_sql というコマンドで OS のタイムゾーン情報を、これらのテーブルに格納するような SQL 文に変換することができます。

# mysql_tzinfo_to_sql /usr/share/zoneinfo
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_
zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type
_id) VALUES
 (@time_zone_id, -2147483648, 0)
,(@time_zone_id, -1830383032, 1)
;
〜後略〜

このクエリを次のようにして mysql データベースで実行するとタイムゾーンが登録されます。

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot mysql

これで Japan のようなシンボルで指定することができるようになります。

mysql> set time_zone = 'Japan';
mysql> select @@system_time_zone, @@global.time_zone, @@time_zone;
+--------------------+--------------------+-------------+
| @@system_time_zone | @@global.time_zone | @@time_zone |
+--------------------+--------------------+-------------+
| UTC                | SYSTEM             | Japan       |
+--------------------+--------------------+-------------+

なお、time_zone が SYSTEM の場合はシステムのタイムゾーンが使われるので、夏時間も正しく処理されます。

自分は夏時間が絡むようなシステムは作ったことないのであまり知見がないのですが、時刻は UTC で登録しておいて、アプリ側で必要に応じて変換する方がいいと思っています。なんとなく…。