MySQL 8.0.19 のオフセットつき日時リテラル

MySQL 8.0.19 のリリースノートにこんなのがあって、

MySQL now supports datetime literals with time zone offsets, such as '2019-12-11 10:40:30-05:00', '2003-04-14 03:30:00+10:00', and '2020-01-01 15:35:45+05:30'; these offsets are respected but not stored when inserting such values into TIMESTAMP and DATETIME columns; that is, offsets are not displayed when retrieving the values.

一瞬 DATETIME 型にタイムゾーンが来たか！と喜んだんだけど、よく読んだら違った。

日時リテラルにオフセットを指定できるようになっただけだった。

日本時間のサーバーで +09:00 を指定しても時刻はそのまま。当たり前。

mysql> SELECT CONVERT('2020-01-28 00:00:00+09:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+09:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 00:00:00                            |
+------------------------------------------------+

UTC(+00:00)を指定すると、日本時間に変換される。

mysql> SELECT CONVERT('2020-01-28 00:00:00+00:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+00:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 09:00:00                            |
+------------------------------------------------+

正確にはサーバーの時間じゃなくて、接続のタイムゾーンに依存する。デフォルトの time_zone が SYSTEM だからサーバーの時間になっていただけ。

接続のタイムゾーンを UTC にすると、UTC時間に変換される。

mysql> SET time_zone='+00:00';

mysql> SELECT CONVERT('2020-01-28 00:00:00+09:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+09:00', DATETIME) |
+------------------------------------------------+
| 2020-01-27 15:00:00                            |
+------------------------------------------------+

mysql> SELECT CONVERT('2020-01-28 00:00:00+00:00', DATETIME);
+------------------------------------------------+
| CONVERT('2020-01-28 00:00:00+00:00', DATETIME) |
+------------------------------------------------+
| 2020-01-28 00:00:00                            |
+------------------------------------------------+

DATETIME 型はタイムゾーンを保持しないので、格納したデータは接続のタイムゾーンを変更しても何も変わらない。残念。TIMESTAMP の方は変わる。

mysql> CREATE TABLE x (ts TIMESTAMP, dt DATETIME);

mysql> SET time_zone='+09:00';
mysql> INSERT INTO x (ts, dt) VALUES
    -> ('2020-01-28 00:00:00+09:00', '2020-01-28 00:00:00+09:00'),
    -> ('2020-01-28 00:00:00+00:00', '2020-01-28 00:00:00+00:00');
mysql> SELECT * FROM x;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2020-01-28 00:00:00 | 2020-01-28 00:00:00 |
| 2020-01-28 09:00:00 | 2020-01-28 09:00:00 |
+---------------------+---------------------+
mysql> SET time_zone='+00:00';
mysql> SELECT * FROM x;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2020-01-27 15:00:00 | 2020-01-28 00:00:00 |
| 2020-01-28 00:00:00 | 2020-01-28 09:00:00 |
+---------------------+---------------------+

おまけ: MySQL 8.0 での TIMESTAMP と DATETIME の比較

昔は TIMESTAMP はレコードの作成時刻や更新時刻を自動設定できるのに DATETIME ではできなかったんだけど、今は DATETIME でもできる。

	TIMESTAMP	DATETIME
上限	2038-01-19 03:14:07+00:00	9999-12-31 23:59:59+00:00
レコード作成時時刻を自動設定	可(デフォルトはオフ)	可(デフォルトはオフ)
レコードの更新時刻を自動設定	可(デフォルトはオフ)	可(デフォルトはオフ)
接続のタイムゾーンに時刻を変換	可	不可

ということで、DATETIME が時刻変換してくれるようになれば、TIMESTAMP を捨てされるのになーって感じ。