はじめに

MySQL には日時を扱う型として「TIMESTAMP」と「DATETIME」があります。 これらの違いの１つとして、TIMESTAMP 型はタイムゾーン設定の影響を受けますが、DATETIME は受けないということがあります。

今日はその具体的な挙動について、サンプル SQL と併せてご紹介します。

今回は MySQL 互換の MariaDB 10.4 で動作確認しています。

目次

1. MySQL の TIMESTAMP 型について
2. 実際に試してみる
3. おわりに

1. MySQL の TIMESTAMP 型について

TIMESTAMP 型は保存する際に、日付データを UTC +00:00 に変換します。 そして取り出す際は、設定されているタイムゾーンの時間に戻す処理を行います。

MySQL :: MySQL 5.7 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

「MySQL converts TIMESTAMP values from the current time zone to UTC ～」の箇所に書いてあります。

一方、DATETIME 型は、設定されているタイムゾーンに関係なく、挿入した時間をそのまま保存し、取得します。 つまり、タイムゾーンの設定に関わらず、常に同じ値が返ってきます。

今回の実験では、タイムゾーンを Asia/Tokyo に設定したうえで、TIMESTAMP 型と DATETIME 型のフィールドに同じ日時を保存し、 Asia/Tokyo と UTC の設定でそれらを取得して比較します。

2. 実際に試してみる

まずは下記 SQL でテーブルを作成します。

CREATE TABLE `times` (
  `id` int(11) NOT NULL,
  `time_stamp` timestamp NULL DEFAULT NULL,
  `date_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `times`
  ADD PRIMARY KEY (`id`),
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

次にタイムゾーンを Asia/Tokyo に設定します。

MariaDB [sample]> SET time_zone = 'Asia/Tokyo';
Query OK, 0 rows affected (0.000 sec)

MariaDB [sample]> SHOW VARIABLES LIKE 'time_zone';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| time_zone     | Asia/Tokyo |
+---------------+------------+
1 row in set (0.001 sec)

そして、同じ日時を登録。

INSERT INTO times (time_stamp, date_time) VALUES ('2019-12-16 12:23:34', '2019-12-16 12:23:34');

今、タイムゾーンは挿入時と同じ Asia/Tokyo のままなので、取得すればどちらも 2019-12-16 12:23:34 になります。

MariaDB [sample]> SELECT * FROM times;
+----+---------------------+---------------------+
| id | time_stamp          | date_time           |
+----+---------------------+---------------------+
|  1 | 2019-12-16 12:23:34 | 2019-12-16 12:23:34 |
+----+---------------------+---------------------+
1 row in set (0.000 sec)

次に、タイムゾーンを UTC に変更して、取得してみます。

MariaDB [sample]> SET time_zone = 'UTC';
Query OK, 0 rows affected (0.000 sec)

MariaDB [sample]> SELECT * FROM times;
+----+---------------------+---------------------+
| id | time_stamp          | date_time           |
+----+---------------------+---------------------+
|  1 | 2019-12-16 03:23:34 | 2019-12-16 12:23:34 |
+----+---------------------+---------------------+
1 row in set (0.000 sec)

上記結果を見ると、date_time フィールドの値は変わっていませんが、time_stamp の値が変わっています。 その差はマイナス9時間で、Asia/Tokyo は +09:00 なので、その分が減算されていることが分かります。

3. おわりに

今日は MySQL の TIMESTAMP と DATETIME のタイムゾーン設定時の挙動の違いを調べました。DATETIME はタイムゾーンの影響を受けず、登録した日時がそのまま保存されます。一方 TIMEZONE は保存時には UTC 時間に変換し、取得する際は設定中のタイムゾーンでの時間に変換されます。

越境 EC など、多国で使用するシステムでは、日時は TIMESTAMP 型にすると便利かもしれませんね。

ただ、TIMESTAMP 型には 2038 年問題があるんですよね。今後修正されることを期待はしたいですが、現時点で 2038 年以降のデータを使う場合は TIMESTAMP 型は使えませんのでご注意ください。