MySQLのタイムゾーンの挙動差について調べた
作っている場所と使用される場所のタイムゾーンが違ったり、国際的なサービスを作っていたりする場合に、タイムゾーンまわりの挙動について気になったので、MySQLで実際に試したことのまとめ。
前提
- 初期時刻設定
- 試行時刻
- 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)
時刻関数
データ型
- datetime, date, time
- timestamp
まとめ
- 入力される時刻は、時刻関数の挙動に依存する
出力される時刻は、保存されているデータ型の挙動に依存する
- データ型は、タイムゾーン情報を持っている型かによって、挙動のタイプが変化する