MariaDB における Time Zone の取扱い

東京オリンピックを契機に日本でサマータイム(Daylight Saving Time)を、しかもどこの国でも採用していない**2時間**のオフセットでの実施を検討しているらしいです。
特に諸々のICTシステムへの影響が大きすぎるため、関係する方々に全力で闇に葬ってほしいですが、DSTについて自分がコードを書く際に検討をしたことないので、MariaDB での時刻の取扱を検証してみました。

# 検証環境

- MariaDB 10.3.8 GA
- CentOS 7.5.1804

# 公式 Knowledge Base 

https://mariadb.com/kb/en/library/time-zones/

デフォルトでは 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](https://mariadb.com/kb/en/library/timestamp/)データタイプと、[DATETIME](https://mariadb.com/kb/en/library/datetime/)データタイプの比較を行うためのテーブルを作成します。

```sql
create table tz_test (
  timestamp timestamp primary key,
  datetime datetime,
  system_time_zone varchar(10)
)
```

## JST(GMT+9) での INSERT

まずは、OS/MariaDBともに日本標準時間(JST)の設定でデータをINSERTしてみます。

```sql
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 を確認すると、

```sql
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 を確認します。

```sql
MariaDB [test]> select @@system_time_zone; select @@time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| +11                |
+--------------------+

+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
```

正常に ```+11``` に変更されていることが確認できました。

ここで、INSERT を試してみます。

```sql
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 を確認します。

```sql
MariaDB [test]> select @@system_time_zone; select @@time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| JST                |
+--------------------+

+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
```

system_time_zone が JST に戻っていることが確認できます。
tz_testテーブル内のデータを確認します。

```sql
$ 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時間の誤差が生じてしまいます。

```sql
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](https://mariadb.com/resources/blog/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

## テストテーブル

```sql
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します。

```sql
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');
```

タイムゾーンとデータを確認します。

```sql
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太平洋時間](https://ja.wikipedia.org/wiki/%E5%A4%AA%E5%B9%B3%E6%B4%8B%E5%A4%8F%E6%99%82%E9%96%93)に変更します。

```
# timedatectl set-timezone 'America/Los_Angeles'
# systemctl restart mariadb
```

データを確認します。

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