東京オリンピックを契機に日本でサマータイム(Daylight Saving Time)を、しかもどこの国でも採用していない2時間のオフセットでの実施を検討しているらしいです。
特に諸々のICTシステムへの影響が大きすぎるため、関係する方々に全力で闇に葬ってほしいですが、DSTについて自分がコードを書く際に検討をしたことないので、MariaDB での時刻の取扱を検証してみました。
検証環境
- MariaDB 10.3.8 GA
- CentOS 7.5.1804
公式 Knowledge Base
デフォルトでは system_time_zone システム変数にOSのタイムゾーンが設定されます。
$ timedatectl
Local time: Sun 2018-08-12 16:10:48 JST
Universal time: Sun 2018-08-12 07:10:48 UTC
RTC time: Sun 2018-08-12 07:10:47
Time zone: Asia/Tokyo (JST, +0900)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a
OSでタイムゾーンを日本標準時に設定している場合、MariaDBのデフォルト設定では、time_zone と system_time_zone が以下のように設定されています。
$ sudo mysql
Server version: 10.3.8-MariaDB MariaDB Server
MariaDB [(none)]> select @@time_zone; select @@system_time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
+--------------------+
| @@system_time_zone |
+--------------------+
| JST |
+--------------------+
異なるタイムゾーン設定における時刻INSERTテスト
テストテーブル
以下のように、TIMESTAMPデータタイプと、DATETIMEデータタイプの比較を行うためのテーブルを作成します。
create table tz_test (
timestamp timestamp primary key,
datetime datetime,
system_time_zone varchar(10)
)
JST(GMT+9) での INSERT
まずは、OS/MariaDBともに日本標準時間(JST)の設定でデータをINSERTしてみます。
MariaDB [test]> insert into tz_test values (now(), now(), @@system_time_zone);
Query OK, 1 row affected (0.026 sec)
MariaDB [test]> select @@system_time_zone; select @@time_zone; select * from tz_test;
+--------------------+
| @@system_time_zone |
+--------------------+
| JST |
+--------------------+
1 row in set (0.000 sec)
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.000 sec)
+---------------------+---------------------+------------------+
| timestamp | datetime | system_time_zone |
+---------------------+---------------------+------------------+
| 2018-08-12 16:21:13 | 2018-08-12 16:21:13 | JST |
+---------------------+---------------------+------------------+
GMT+11 での INSERT
日本標準時間(GMT+9)から2時間進める場合、GMT+11となります。なお、このタイムゾーンに属するのは、マガダン/ソロモン諸島/ニューカレドニアらしいです。遠いですね。
まずは OS のタイムゾーンをニューカレドニアの首都、ヌメアに変更してみます。
# timedatectl set-timezone "Pacific/Noumea"
# timedatectl
Local time: Sun 2018-08-12 18:29:27 +11
Universal time: Sun 2018-08-12 07:29:27 UTC
RTC time: Sun 2018-08-12 07:29:27
Time zone: Pacific/Noumea (+11, +1100)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a
Local time が UTC+11 になりました。この状態で MariaDB の system_time_zone, time_zone を確認すると、
MariaDB [test]> select @@system_time_zone; select @@time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| JST |
+--------------------+
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
JSTのままです。MariaDB がOSのタイムゾーンを参照するのは起動時のみと推測されますので、systemctl restart mariadb で mysqld を再起動します。
再度 system_time_zone / time_zone を確認します。
MariaDB [test]> select @@system_time_zone; select @@time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| +11 |
+--------------------+
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
正常に +11 に変更されていることが確認できました。
ここで、INSERT を試してみます。
MariaDB [test]> insert into tz_test values (now(), now(), @@system_time_zone);
Query OK, 1 row affected (0.027 sec)
MariaDB [test]> select * from tz_test;
+---------------------+---------------------+------------------+
| timestamp | datetime | system_time_zone |
+---------------------+---------------------+------------------+
| 2018-08-12 18:21:13 | 2018-08-12 16:21:13 | JST |
| 2018-08-12 18:36:30 | 2018-08-12 18:36:30 | +11 |
+---------------------+---------------------+------------------+
TIMESTAMP列はいったんUTCに変換されてテーブルに保存され、SELECTでローカルタイムで表示するのに対して、DATETIME列はUTCへの変換は行わず、ローカルタイムのままテーブルに保存しているようです。
JSTへタイムゾーンを戻す
再び日本標準時間にタイムゾーンを戻し、mariadb service を再起動します。
# timedatectl set-timezone "Asia/Tokyo"
# timedatectl
Local time: Sun 2018-08-12 16:41:06 JST
Universal time: Sun 2018-08-12 07:41:06 UTC
RTC time: Sun 2018-08-12 07:41:06
Time zone: Asia/Tokyo (JST, +0900)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a
# systemctl restart mariadb
念のため、system_time_zone / time_zone を確認します。
MariaDB [test]> select @@system_time_zone; select @@time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| JST |
+--------------------+
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
system_time_zone が JST に戻っていることが確認できます。
tz_testテーブル内のデータを確認します。
$ sudo mysql test
MariaDB [test]> select * from tz_test;
+---------------------+---------------------+------------------+
| timestamp | datetime | system_time_zone |
+---------------------+---------------------+------------------+
| 2018-08-12 16:21:13 | 2018-08-12 16:21:13 | JST |
| 2018-08-12 16:36:30 | 2018-08-12 18:36:30 | +11 |
+---------------------+---------------------+------------------+
DATETIME列のデータはJSTでも、GMT+11でも同一ですが、TIMESTAMP列のデータはOSやMariaDBのタイムゾーン設定に応じてローカルタイムで表示しています。
したがって同一のシステムをJSTで運用していて、そのままサマータイム開始日時にOS, MariaDBの時間を2時間進めて運用してしまうとDATETIMEで時刻を保存しているデータに2時間の誤差が生じてしまいます。
MariaDB [test]> select UNIX_TIMESTAMP(timestamp) - UNIX_TIMESTAMP(datetime), system_time_zone from tz_test;
+------------------------------------------------------+------------------+
| UNIX_TIMESTAMP(timestamp) - UNIX_TIMESTAMP(datetime) | system_time_zone |
+------------------------------------------------------+------------------+
| 0 | JST |
| -7200 | +11 |
+------------------------------------------------------+------------------+
DST開始/終了時のテスト
(今議論されている日本の)サマータイム開始時には2時間の空白時間が、終了時には2時間の重複が生じます。
zoneinfoを書き換えるまでの手間はかけたくないので、
Of Temporal Datatypes, Electricity and Cows
こちらの投稿と同様に、US太平洋時間(PST/PDT)を用いた1時間のオフセット実施時のテストを行ってみます。
https://www.timeanddate.com/time/change/usa/new-york?year=2017
https://www.timeanddate.com/time/change/usa/new-york?year=2016
テストテーブル
CREATE TABLE tz_test2(
id int not null primary key auto_increment,
timestamp timestamp
);
UTC で2016年の DST 終了時の時刻データを INSERT
OS のタイムゾーンを UTC に設定し、MariaDB を再起動します。
# timedatectl set-timezone 'UTC'
# systemctl restart mariadb
PDTのDST終了時に1時間、時間を戻している付近の時刻をUTCに変換し、INSERTします。
INSERT INTO tz_test2 (timestamp) VALUES ('2016-11-06 07:30:00');
INSERT INTO tz_test2 (timestamp) VALUES ('2016-11-06 08:00:00');
INSERT INTO tz_test2 (timestamp) VALUES ('2016-11-06 08:30:00');
INSERT INTO tz_test2 (timestamp) VALUES ('2016-11-06 09:00:00');
タイムゾーンとデータを確認します。
MariaDB [test]> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
MariaDB [test]> select @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| UTC |
+--------------------+
MariaDB [test]> select * from tz_test2 order by timestamp;
+----+---------------------+
| id | timestamp |
+----+---------------------+
| 1 | 2016-11-06 07:30:00 |
| 2 | 2016-11-06 08:00:00 |
| 3 | 2016-11-06 08:30:00 |
| 4 | 2016-11-06 09:00:00 |
+----+---------------------+
タイムゾーンをPDTに変更
OSのタイムゾーンをLAにし、US太平洋時間に変更します。
# timedatectl set-timezone 'America/Los_Angeles'
# systemctl restart mariadb
データを確認します。
MariaDB [test]> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
MariaDB [test]> select @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| PDT |
+--------------------+
MariaDB [test]> select * from tz_test2 order by timestamp;
+----+---------------------+
| id | timestamp |
+----+---------------------+
| 1 | 2016-11-06 00:30:00 |
| 2 | 2016-11-06 01:00:00 |
| 3 | 2016-11-06 01:30:00 |
| 4 | 2016-11-06 01:00:00 |
+----+---------------------+
DST終了時には1時間、時間が戻りますので、 1:00 が重複 しています。したがって時刻同士の演算等を行う場合は UTC ベースで行う必要がありそうです。
空白時間のテスト
反対にDST開始時には1時間、時間が飛びますので1時間の空白が生じます。以下のようにこの空白時間にデータをINSERTしようとするとエラーが発生します。
MariaDB [test]> INSERT INTO tz_test2 (timestamp) VALUES ('2017-03-12 02:30:00');
ERROR 1292 (22007): Incorrect datetime value: '2017-03-12 02:30:00' for column 'timestamp' at row 1
まとめ
- MariaDB の time_zone システム変数はデフォルトで SYSTEM となっており、MariaDB起動時にOSのタイムゾーンや環境変数TZからタイムゾーンを取得、 system_time_zone システム変数に反映させる
- OSのタイムゾーンが変更されても、MariaDB を再起動しないとタイムゾーン変更されない
- time_zone システム変数を動的に
SET time_zone = '+11:00';のように変更することも可能 - TIMESTAMP列ではいったんUTCに変換されてから保存され、参照時にローカルタイムに再変換されるため、タイムゾーンにかかわらずUTCでデータが保存される
- DATETIME列ではUTCへの変換が行われないため、同じシステムを異なるタイムゾーンで運用すると、一貫性が失われる恐れあり。なお、Rails の created_at, updated_at は DATETIME データタイプを用い、UTCで時刻が保存されている。
- DST開始時には空白が生じるので、この時間帯のTIMESTAMPを保存しようとするとエラーとなる
日本にずっといるとDST開始/終了を意識することがないので勉強にはなりましたが、こんな検証とその後のコード修正を日本中のネットワーク機器、ミドルウェア、アプリケーションなどすべてで来年夏までに実施するのはムリだと思います...