MySQLの日時に関する話

MySQLには日時を扱うデータ型として DATE, TIME, DATETIME, TIMESTAMP, YEAR が用意されています。

DATE, TIME, DATETIME, YEAR はだいたい想像通りに動きます。入れた値がそのまま取り出せます。そんなのデータベースだから当たり前のことなんですが、TIMESTAMP は一見するとそうではない。データの入出力時にタイムゾーン変換を行うのです。これちゃんと意識してないと、データが壊れたんじゃないかと錯覚しかねません。って、そんな誤解をしてしまう抜け作は私くらいのものだとは思いますけれども。

例えばタイムゾーンが JST に設定されていたとします。'2021-06-19 11:00:00' というデータを格納しようとすると、これが UTC に変換されて MySQL には '2021-06-19 02:00:00' が格納されるわけです。データを取りだすときにも逆のタイムゾーン変換が行われますので '2021-06-19 11:00:00' が取り出されます。

```sql
root@localhost [test]> CREATE TABLE items (
    -> id INTEGER AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(255),
    -> published_at TIMESTAMP
    -> );
Query OK, 0 rows affected (0.06 sec)

root@localhost [test]> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.03 sec)

root@localhost [test]> INSERT INTO items (name, published_at)
    -> VALUES (
    -> 'MySQLコネタ集ちょっと足し', '2021-06-19 11:00:00'
    -> );
Query OK, 1 row affected (0.02 sec)

root@localhost [test]> SELECT * FROM items;
+----+-------------------------------------+---------------------+
| id | name                                | published_at        |
+----+-------------------------------------+---------------------+
|  1 | MySQLコネタ集ちょっと足し           | 2021-06-19 11:00:00 |
+----+-------------------------------------+---------------------+
1 row in set (0.00 sec)
```

つまり、タイムゾーンについて何も考えてなくても、一見すると入れたデータがそのまま出てきているように見えてしまう。そして将来何かの機会で躓いてしまう。まあ、ありがちな話ですね。例えば先ほどのデータをPST(太平洋夏時間、-0800 ですが実際にはサマータイムのため -0700)で取り出すと '2021-06-18 19:00:00' と日付まで違ってしまって驚くわけです。

```sql
root@localhost [test]> SHOW VARIABLES LIKE '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | JST                 |
| time_zone        | America/Los_Angeles |
+------------------+---------------------+
2 rows in set (0.01 sec)

root@localhost [test]> SELECT * FROM items;
+----+-------------------------------------+---------------------+
| id | name                                | published_at        |
+----+-------------------------------------+---------------------+
|  1 | MySQLコネタ集ちょっと足し           | 2021-06-18 19:00:00 |
+----+-------------------------------------+---------------------+
1 row in set (0.02 sec)
```

ということで、TIMESTAMP 型を扱うならタイムゾーンはちゃんと意識しましょうということですね。

TIMESTAMP 型の注意点は他にもあります。TIMESTAMP ではエポック秒で保存されますので、'1970-01-01 00:00:01' から '2038-01-19 03:14:07' の値が格納できます。そう。MySQLではエポック秒が32ビットなので2038年問題を抱えているのですね。OS や言語が2038年問題対応済みだからと安心していたら、MySQL で詰んだってことが起こりかねません。2038年まであと17年ですから、まだ先と思いつつも、そろそろ考えておかないと先々に困るでしょう。その頃には読者のみなさんや私はもう現場にはいないかもしれませんが、その時の担当者がとても困ることは目に見えています。少なくともこれから構築するシステムでは TIMESTAMP 型を採用するかどうかはよくよく考えてから行った方がいいと思います。

そしてエポック秒とタイムゾーンが組み合わさることで更に注意が必要です。MySQL には常に UTC で保存されますので、JST で '1970-01-01 00:00:01' を保存しようとするとタイムゾーン変換されて '1969-12-31 15:00:01' を格納しようとしてエラーとなってしまいます。

```sql
root@localhost [test]> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

root@localhost [test]> INSERT INTO items (name, published_at)
    -> VALUES (
    -> 'とても古い本', '1970-01-01 00:00:00'
    -> );
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for
column 'published_at' at row 1
```

しかし逆は大丈夫です。'1970-01-01 00:00:01' が保存されていて、PST で取り出すと '1969-12-31 16:00:01' が得られます。

```sql
root@localhost [test]> SHOW VARIABLES LIKE '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | JST                 |
| time_zone        | America/Los_Angeles |
+------------------+---------------------+
2 rows in set (0.01 sec)

root@localhost [test]> SELECT * FROM items;
+----+--------------------+---------------------+
| id | name               | published_at        |
+----+--------------------+---------------------+
|  1 | とても古い本       | 1969-12-31 16:00:01 |
+----+--------------------+---------------------+
1 row in set (0.02 sec)
```

では TIMESTAMP 型以外には何の注意点も無いかというと、そうでもありません。設定によっては、日付に 0 が保存出来てしまうのです。'0000-00-00' とか '2021-00-00' とかですね。MySQL には SQL_MODE という設定があり、ここに NO_ZERO_IN_DATE や NO_ZERO_DATE が設定されていると、こうした0日付は保存出来なくなります。最近の MySQL ではデフォルトでこれらの設定が有効になっているのですが、以前の MySQL にはそもそもこんな設定ありませんでしたので、平気で0日付が保存されていたりするわけです。0日付が格納されたデータがあるため、MySQL のバージョンを上げられなくて困ってるというシステムをこれまであちこちで見ました。