MySQLのタイムゾーンの挙動差について調べた - 君たちは永遠にそいつらより若い

作っている場所と使用される場所のタイムゾーンが違ったり、国際的なサービスを作っていたりする場合に、タイムゾーンまわりの挙動について気になったので、MySQLで実際に試したことのまとめ。

前提

初期時刻設定
- global.system_time_zone: OSのタイムゾーン（ICTゾーンに住んでるのでICT）
- global.time_zone: MySQLのタイムゾーン（OSのタイムゾーンと同期）
- session.time_zone: MySQLの該当セッションのタイムゾーン（OSのタイムゾーンと同期）
試行時刻
- ICT 2015-04-17 02:18 にテスト
試行方法
- session.time_zone を ICT(+7:00), JST(+9:00) に設定を切り替え、連続してデータを挿入

mysql> SELECT @@global.system_time_zone, @@global.time_zone, @@session.time_zone;
+---------------------------+--------------------+---------------------+
| @@global.system_time_zone | @@global.time_zone | @@session.time_zone |
+---------------------------+--------------------+---------------------+
| ICT                       | SYSTEM             | SYSTEM              |
+---------------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> desc test_times;                                                                         
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| type      | varchar(20) | YES  |     | NULL              |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| datetime  | datetime    | YES  |     | NULL              |                             |
| date      | date        | YES  |     | NULL              |                             |
| time      | time        | YES  |     | NULL              |                             |
+-----------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> SET SESSION time_zone = '+07:00';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_times values ('2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_times values ('now()', now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into test_times values ('utc_timestamp()', utc_timestamp(), utc_timestamp(), utc_timestamp(), utc_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET SESSION time_zone = '+09:00';                                                              
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_times values ('2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00', '2000-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_times values ('now()', now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into test_times values ('utc_timestamp()', utc_timestamp(), utc_timestamp(), utc_timestamp(), utc_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)

結果

mysql> SET SESSION time_zone = '+07:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_times;
+---------------------+---------------------+---------------------+------------+-----------+
| type                | timestamp           | datetime            | date       | time      |
+---------------------+---------------------+---------------------+------------+-----------+
| 2000-01-01 00:00:00 | 2000-01-01 00:00:00 | 2000-01-01 00:00:00 | 2000-01-01 | 00:00:00  |
| now()               | 2015-04-17 02:18:06 | 2015-04-17 02:18:06 | 2015-04-17 | 02:18:06  |
| utc_timestamp()     | 2015-04-16 19:18:06 | 2015-04-16 19:18:06 | 2015-04-16 | 19:18:06  |
| 2000-01-01 00:00:00 | 1999-12-31 22:00:00 | 2000-01-01 00:00:00 | 2000-01-01 | 00:00:00  |
| now()               | 2015-04-17 02:18:15 | 2015-04-17 04:18:15 | 2015-04-17 | 04:18:15  |
| utc_timestamp()     | 2015-04-16 17:18:15 | 2015-04-16 19:18:15 | 2015-04-16 | 19:18:15  |
+---------------------+---------------------+---------------------+------------+-----------+
6 rows in set (0.00 sec)

mysql> SET SESSION time_zone = '+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_times;
+---------------------+---------------------+---------------------+------------+-----------+
| type                | timestamp           | datetime            | date       | time      |
+---------------------+---------------------+---------------------+------------+-----------+
| 2000-01-01 00:00:00 | 2000-01-01 02:00:00 | 2000-01-01 00:00:00 | 2000-01-01 | 00:00:00  |
| now()               | 2015-04-17 04:18:06 | 2015-04-17 02:18:06 | 2015-04-17 | 02:18:06  |
| utc_timestamp()     | 2015-04-16 21:18:06 | 2015-04-16 19:18:06 | 2015-04-16 | 19:18:06  |
| 2000-01-01 00:00:00 | 2000-01-01 00:00:00 | 2000-01-01 00:00:00 | 2000-01-01 | 00:00:00  |
| now()               | 2015-04-17 04:18:15 | 2015-04-17 04:18:15 | 2015-04-17 | 04:18:15  |
| utc_timestamp()     | 2015-04-16 19:18:15 | 2015-04-16 19:18:15 | 2015-04-16 | 19:18:15  |
+---------------------+---------------------+---------------------+------------+-----------+
6 rows in set (0.00 sec)

時刻関数

具体的時刻(2000-01-01 00:00:00)
- 具体的時刻がそのまま挿入される
now()
- 設定されたタイムゾーンに合わせて、現在時刻が挿入される
utc_timestamp()
- 設定されたタイムゾーンを無視して、UTC タイムゾーンの現在時刻が挿入される

データ型

datetime, date, time
- datetime は、'1000-01-01 00:00:00' から '9999-12-31 23:59:59' のレンジ
- 「時刻」のみが記録される時刻の型である
- データ挿入時の該当タイムゾーンの時刻が、「そのままの時刻」データとして挿入される
- つまり、タイムゾーン情報は持っていないので、表示時にはタイムゾーンに影響されない。システムのタイムゾーンを動かしても、見かけの値は変わらない。
timestamp
- timestampは、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC のレンジ
- 「時刻とタイムゾーン」がセットで記録される時刻の型である
- データ挿入時の該当タイムゾーンの時刻が、「UTC タイムゾーンの時刻」に変換され、「UTC タイムゾーンの時刻」データとして挿入される
- つまり、タイムゾーン情報を持っているので、表示時にタイムゾーンに影響される。システムのタイムゾーンを動かすと、見かけの値がシステムのタイムゾーンに従って変わる。

まとめ

入力される時刻は、時刻関数の挙動に依存する
出力される時刻は、保存されているデータ型の挙動に依存する
- データ型は、タイムゾーン情報を持っている型かによって、挙動のタイプが変化する
MySQLのタイムゾーン系のドキュメントはこちら。
- MySQL :: MySQL 5.7 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
- MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support