知らなかったでは済まされない！MySQLのDATETIMEとTIMESTAMPの落とし穴

2024-04-03

MySQLにおけるDATETIMEとTIMESTAMPの使い分け

答え: どちらを使用するかは、以下の要件によって異なります。

格納したい日時範囲

DATETIME: 1000-01-01 00:00:00 から 9999-12-31 23:59:59.999999 まで
TIMESTAMP: 1970-01-01 00:00:01 から 2038-01-19 03:14:07 まで

タイムゾーンの扱い

DATETIME: 格納時にタイムゾーン変換は行われず、クライアントのタイムゾーンで格納されます。
TIMESTAMP: 格納時にUTCへ変換され、常にUTCで格納されます。

自動更新

DATETIME: 自動更新されません。
TIMESTAMP: CURRENT_TIMESTAMP を使用すると、レコード挿入・更新時に自動的に現在のタイムスタンプが格納されます。

桁数

DATETIME: 19桁 (YYYY-MM-DD HH:MM:SS.fffffffff)
TIMESTAMP: 14桁 (YYYY-MM-DD HH:MM:SS) または 19桁 (YYYY-MM-DD HH:MM:SS.fffffffff)

その他

TIMESTAMPは、DATETIMEよりも新しいデータ型です。
TIMESTAMPは、一部の古いデータベースと互換性がない場合があります。

それぞれのデータ型が適しているケース

DATETIME
- 広い範囲の日時を格納したい場合
- タイムゾーンを保持したい場合
- 自動更新の必要がない場合
TIMESTAMP
- 2038年以前の日時を格納する場合
- 自動更新したい場合
- 少ない桁数で格納したい場合

補足

上記はMySQL 8.0時点の情報です。バージョンによって挙動が異なる場合があります。
特に、TIMESTAMP型の自動更新の挙動はバージョンによって大きく変更されています。

DATETIMEとTIMESTAMPの格納と比較

-- DATETIMEとTIMESTAMPの格納

INSERT INTO `test` (`datetime_col`, `timestamp_col`) VALUES
  ('2024-04-03 01:13:00', CURRENT_TIMESTAMP());

-- DATETIMEとTIMESTAMPの比較

SELECT `datetime_col`, `timestamp_col` FROM `test`;

-- 結果

-- datetime_col | timestamp_col
-- ------------- | -------------
-- 2024-04-03 01:13:00 | 2024-04-03 01:13:00.000000

DATETIMEとTIMESTAMPのタイムゾーン変換

-- DATETIMEのタイムゾーン変換

SET time_zone = '+09:00';

INSERT INTO `test` (`datetime_col`) VALUES ('2024-04-03 01:13:00');

SELECT `datetime_col` FROM `test`;

-- 結果

-- datetime_col
-- -------------
-- 2024-04-03 10:13:00

-- TIMESTAMPのタイムゾーン変換

SELECT `timestamp_col` FROM `test`;

-- 結果

-- timestamp_col
-- -------------
-- 2024-04-02 16:13:00.000000

TIMESTAMPの自動更新

-- TIMESTAMPの自動更新

INSERT INTO `test` (`timestamp_col`) VALUES (CURRENT_TIMESTAMP());

UPDATE `test` SET `timestamp_col` = CURRENT_TIMESTAMP();

SELECT `timestamp_col` FROM `test`;

-- 結果

-- timestamp_col
-- -------------
-- 2024-04-03 01:13:00.000000