[MySQL]DATETIME型とTIMESTAMP型の違い

はじめに

個人開発でDB設計を行っていた際、MySQLのDATETIME型とTIMESTAMP型の違いが曖昧だったため、ここで整理してみました。

基本情報

DATETIME

• 日付と時間の両方を扱うことができる
• サポートしている範囲は 1000-01-01 00:00:00 から 9999-12-31 23:59:59
• 日時の情報をYYYY-MM-DD hh:mm:ssの形式で保存する

TIMESTAMP

• 日付と時間の両方を扱うことができる
• サポートしている範囲は1970-01-01 00:00:01 UTC から 2038-01-19 03:14:07 UTC の範囲
• 内部的には1970年1月1日00:00:01 UTC（Unixエポック）からの経過秒数として保存する

保存方法の違いについて

結論としては、「タイムゾーンの影響を受けるか」です。
DATETIMEの場合、日本時間（UTC+9）で「2024-07-14 12:00:00」として保存された日時が、アメリカ東部時間（UTC-5）のユーザーから見ても同じ「2024-07-14 12:00:00」として表示されます。この場合、実際の時間のずれ（14時間）が考慮されません。
TIMESTAMP型はUTC（協定世界時）で保存され、サーバーやクライアントのタイムゾーンに基づいて表示(クライアントのタイムゾーンを元に変換)される。

実際に検証(※mysql8.2のdocker imageを使用)

テーブル準備

-- DATETIME用
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATETIME NOT NULL
);

INSERT INTO events (event_name, event_date) VALUES ('Meeting', '2024-07-14 12:00:00');

-- TIMESTAMP用
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('System started');

タイムゾーンを変えて値を比較

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| UTC                |
+--------------------+

mysql> select * from events;
+----+------------+---------------------+
| id | event_name | event_date          |
+----+------------+---------------------+
|  1 | Meeting    | 2024-07-14 12:00:00 |
+----+------------+---------------------+


mysql> select * from logs;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | System started | 2024-07-16 12:51:26 |
+----+----------------+---------------------+

-- タイムゾーンを変更
mysql> SET time_zone = 'America/New_York';
Query OK, 0 rows affected (0.00 sec)

-- DATETIMEは変わらない
mysql> select * from events;
+----+------------+---------------------+
| id | event_name | event_date          |
+----+------------+---------------------+
|  1 | Meeting    | 2024-07-14 12:00:00 |
+----+------------+---------------------+

-- TIMESTAMPはタイムゾーンを考慮してくれる
mysql> select * from logs;
+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | System started | 2024-07-16 08:51:26 |
+----+----------------+---------------------+

summary

datetimeのメリット

• タイムゾーンの影響を受けず、保存した日時はそのまま保持される
• 幅広い日時範囲をサポートできる

timestampのメリット

• 異なるタイムゾーンのユーザーに対しても一貫した時間の取り扱いが可能
  -> 異なるタイムゾーンのユーザーが存在するシステムでは、TIMESTAMP型を使用する方が適している

余談

今までTIMESTAMPとDATETIMEなんて自動更新できるかの違い(updated_atはTIMESTAMPとか🥹)かと思っていたけど、違った。
しかもMySQL8からどちらも自動更新できるようになってました💦

参考URL

11.2.2 DATE、DATETIME、および TIMESTAMP 型(MySQL公式)

11.2.5 TIMESTAMP および DATETIME の自動初期化および更新機能(MySQL公式)