MySQL Timezoneまとめ

やりたいこと

前提知識

MySQLサーバ側のタイムゾーンの概念

  • System time zone: サーバ起動時に指定するタイムゾーン。基本的に不変。使い所よくわかんない。
  • Server current time zone: サーバが現時点で使ってるタイムゾーン。デフォルトではSystem time zoneと同じ。
    • 以下のいずれかの方法で変更可能
      • MySQLサーバ起動時に --default-time-zone='Asia/Bangkok' のように指定する
      • 管理者権限( SYSTEM_VARIABLES_ADMIN 権限)で SET GLOBAL time_zone = 'Aisa/Bangkok' のように指定する
  • Per-session time zone: Sessionごとのタイムゾーン。 SET time_zone = 'Asia/Bangkok' のように変更可能

参考

dev.mysql.com

データ型 DATETIME と TIMESTAMP

  • DATETIME: 日時を持つ。基本的にタイムゾーンの概念はない。
  • TIMESTAMP: 内部的にEpoch秒を持つ。以下の特徴を持つ。

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.

参考

dev.mysql.com

実験

前提条件

対応内容

以下のようなテーブルを作り、セッションおよびJDBCのパラメータを変えながらタイムスタンプの値を見ていく。

mysql> CREATE TABLE tz_sample
    -> (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   system_tz VARCHAR(256),
    ->   session_tz VARCHAR(256),
    ->   raw_datetime VARCHAR(256),
    ->   jdbc_param TEXT,
    ->   datetime DATETIME,
    ->   timestamp TIMESTAMP
    -> );
Query OK, 0 rows affected (0.04 sec)

INSERT文は基本的に以下で固定。

INSERT INTO tz_sample
(system_tz, session_tz, raw_datetime, jdbc_param, datetime, timestamp)
VALUES
('Asia/Bangkok', 'そのときのセッションのタイムゾーン', '2021-02-02T12:00:00+00:00', '2021/02/02 12:00:00', '2021/02/02 12:00:00');

確認項目

データ確認

  • TIMESTAMP 型に日時を保存する際にタイムゾーンを明示しない場合、現在のセッションのタイムゾーンの日時として認識される。
    • '12:00' だと、タイ時間の昼12時 = UTCの朝5時となる。
  • TIMESTAMP 型は常にUTCで時刻を保存している。このため、データを読み出すときは、セッションのタイムゾーンによって表示される時刻が変わる。
  • DATETIME型はタイムゾーン情報を持たない。よって、セッションのタイムゾーンを変更しても、表示される時刻は同じ。

Timezone = Asia/Bangkokで読み出す

mysql> SELECT @@system_time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@system_time_zone | @@session.time_zone |
+--------------------+---------------------+
| +07                | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT id, system_tz, session_tz, raw_datetime, datetime, timestamp FROM tz_sample WHERE jdbc_param IS NULL;
+----+--------------+------------------+---------------------------+---------------------+---------------------+
| id | system_tz    | session_tz       | raw_datetime              | datetime            | timestamp           |
+----+--------------+------------------+---------------------------+---------------------+---------------------+
|  1 | Asia/Bangkok | Asia/Bangkok     | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 12:00:00 |
|  2 | Asia/Bangkok | UTC              | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 19:00:00 |
|  3 | Asia/Bangkok | Australia/Sydney | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 08:00:00 |
+----+--------------+------------------+---------------------------+---------------------+---------------------+

Timezone = UTCで読み出す

mysql> SET time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, system_tz, session_tz, raw_datetime, datetime, timestamp FROM tz_sample WHERE jdbc_param IS NULL;
+----+--------------+------------------+---------------------------+---------------------+---------------------+
| id | system_tz    | session_tz       | raw_datetime              | datetime            | timestamp           |
+----+--------------+------------------+---------------------------+---------------------+---------------------+
|  1 | Asia/Bangkok | Asia/Bangkok     | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 05:00:00 |
|  2 | Asia/Bangkok | UTC              | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 12:00:00 |
|  3 | Asia/Bangkok | Australia/Sydney | 2021-02-02T12:00:00+00:00 | 2021-02-02 12:00:00 | 2021-02-12 01:00:00 |
+----+--------------+------------------+---------------------------+---------------------+---------------------+
3 rows in set (0.00 sec)