MySQLのDATETIME型とTIMESTAMP型の違いとは？特徴と使い分けを徹底解説

DATETIME型とTIMESTAMP型は、どちらも「YYYY-MM-DD HH:MM:SS」という形式で日付と時刻の両方を格納できるデータ型です。よく似ていますが、実際にはいくつかの重要な違いがあります。ここでは、それぞれの特徴を詳しく見ていきましょう。

DATETIME型とTIMESTAMP型の主な違い

1. 格納できる範囲

DATETIME型：「1000-01-01 00:00:00」から「9999-12-31 23:59:59」までの広い範囲の日付・時刻を扱えます。
TIMESTAMP型：「1970-01-01 00:00:01」から「2038-01-19 08:44:07」（UTC基準）までと、範囲が限定されます。これはUNIXタイムスタンプを32ビット整数で表現しているためで、いわゆる「2038年問題」の原因にもなっています。

2. 必要なストレージサイズ

DATETIME型は5バイト、TIMESTAMP型は4バイトを必要とします。小数秒を格納する場合は、それぞれ最大3バイトが追加されます。なお、MySQL 5.6.4より前のバージョンでは、DATETIME型は8バイトを使用していました。

3. タイムゾーン変換

MySQL 5以降では、TIMESTAMP型の値は保存時にセッションのタイムゾーンからUTCへ変換され、取得時に再びセッションのタイムゾーンへ戻されます。一方、DATETIME型はタイムゾーンによる変換を行わず、入力された値がそのまま保存されます。

4. インデックス

どちらの型の列にもインデックスを作成できます。ただし、TIMESTAMP型は内部的にUTCで値を保持するため、タイムゾーン設定が異なる環境間で範囲検索を行う場合は、結果の解釈に注意が必要です。

5. クエリキャッシュへの影響

NOW()やCURRENT_TIMESTAMP()のように実行ごとに結果が変わる非決定的な関数を含むクエリは、クエリキャッシュの対象になりません。なお、クエリキャッシュ機能自体はMySQL 5.7.20で非推奨となり、MySQL 8.0で削除されています。

それでは、両者の違いを実際に確認できる具体例を見てみましょう。

実行例

mysql> Create table test_datetime(time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO test_datetime (time) values (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.04 sec)

mysql> Select * from test_datetime;
+---------------------+
| time                |
+---------------------+
| 2017-11-14 17:29:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> Create table test_timestamp(time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.64 sec)

mysql> INSERT INTO test_timestamp (time) values (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.06 sec)

mysql> Select * from test_timestamp;
+---------------------+
| time                |
+---------------------+
| 2017-11-14 17:29:50 |
+---------------------+
1 row in set (0.00 sec)

次に、セッションのタイムゾーンをUTC-05:00に変更してから、同じテーブルを参照してみます。

mysql> SET @@session.time_zone = '-5:00';
Query OK, 0 rows affected (0.00 sec)

mysql> Select * from test_timestamp;
+---------------------+
| time                |
+---------------------+
| 2017-11-14 06:59:50 |
+---------------------+
1 row in set (0.00 sec)

実行結果

TIMESTAMP型のテーブルでは、タイムゾーンの変更に応じて表示時刻が「17:29:50」から「06:59:50」へと自動的に変換されました。これは、TIMESTAMP型が値をUTCで保持しているためです。

mysql> Select * from test_datetime;
+---------------------+
| time                |
+---------------------+
| 2017-11-14 17:29:03 |
+---------------------+
1 row in set (0.00 sec)

一方、DATETIME型のテーブルでは、タイムゾーンを変更しても値は「2017-11-14 17:29:03」のまま変わりません。この結果から、複数のタイムゾーンを扱うグローバルなサービスにはTIMESTAMP型が、タイムゾーンに依存しない固定の日時を保存したい場合にはDATETIME型が適していることがわかります。用途に応じて正しく使い分けることが、堅牢なデータベース設計のポイントです。