search
LoginSignup
10

More than 3 years have passed since last update.

posted at

updated at

MariaDB における Time Zone の取扱い

東京オリンピックを契機に日本でサマータイム(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開始/終了を意識することがないので勉強にはなりましたが、こんな検証とその後のコード修正を日本中のネットワーク機器、ミドルウェア、アプリケーションなどすべてで来年夏までに実施するのはムリだと思います...

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
10