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

君たちは永遠にそいつらより若い

技術と音楽と日々のこと。

MySQLのタイムゾーンの挙動差について調べた



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

前提

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)
時刻関数
データ型

まとめ