MySQL
mysql5.6
MySQL8.0

MySQLのtimestamp型に起きる2038年問題

概要

MySQLのtimestamp型は2038年問題が発生すると聞きました。
自分たちは結構ガッツリtimestamp型を使っているので、動作を確認していきます。

対象はMySQL5.6です。

TL;DR

  • timestamp型は2038年問題に直面する
  • 対策としては、DATETIME型にするかINT型でUNIXタイムスタンプを管理するか
    • 無難なのはUNIXタイムスタンプを管理する方

動作確認

まずは適当なテーブルを作成し、データを登録していきます。

date.sql
CREATE TABLE `date` (
  `date` datetime DEFAULT NULL COMMENT 'データ登録日時',
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'タイムスタンプ'
) ENGINE=InnoDB DEFAULT CHARSET=ujis

次に2038年問題が発生するギリギリのデータを登録してみます。
この時点ではどちらも正しく登録されております。

mysql> INSERT INTO date(date, stamp) VALUES ('2038-01-19 12:14:07', '2038-01-19 12:14:07');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM date;
+---------------------+---------------------+
| date                | stamp               |
+---------------------+---------------------+
| 2038-01-19 12:14:07 | 2038-01-19 12:14:07 |
+---------------------+---------------------+
1 row in set (0.00 sec)

次に1秒加算した値を登録してみます。
するとtimestamp型はZeroDateになりました。

mysql> INSERT INTO date(date, stamp) VALUES ('2038-01-19 12:14:08', '2038-01-19 12:14:08');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM date;
+---------------------+---------------------+
| date                | stamp               |
+---------------------+---------------------+
| 2038-01-19 12:14:07 | 2038-01-19 12:14:07 |
| 2038-01-19 12:14:08 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

そのため、 2038-01-19 12:14:07を超えると、stamp型のフィールドは正しい値が入力されなくなります…。
※STRICT_TRANS_TABLESを設定していると、Insertに失敗します。

対策

1. DATETIME型に変更する

DATETIME型が影響を受けないことは、上記で確認ができているので、一つの選択肢となりえます。

課題

DATETIME型は、タイムゾーンの影響を受けません。
そのため、タイムゾーンがUTC→JSTに切り替えても同じ値が入り、日付データが絶対値とならない可能性があります。
下記を見てもらうと、タイムゾーンが変わった後も、 date の値が変わっていないことがわかると思います。

mysql> SELECT * FROM date;
+---------------------+---------------------+
| date                | stamp               |
+---------------------+---------------------+
| 2038-01-19 12:14:07 | 2038-01-19 12:14:07 |
| 2038-01-19 12:14:08 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.01 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM date;
+---------------------+---------------------+
| date                | stamp               |
+---------------------+---------------------+
| 2038-01-19 12:14:07 | 2038-01-19 03:14:07 |
| 2038-01-19 12:14:08 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

2. INT型にタイムスタンプの絶対値を入れるようにする

INT型にしてタイムスタンプの値を入れてしまう案です。
これなら、DATETIME型で起きる問題は解決されます。

日本語で PHP の話をする Slack のグループを作ったよ【参加者募集】 - 頭ん中で話題に上がっていた解決方法で、自力で編み出せてないです。

mysql> INSERT INTO date(date, stamp,unix_timestamp) VALUES ('2038-01-19 12:14:07', '2038-01-19 12:14:07', unix_timestamp('2038-01-19 12:14:07'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *,from_unixtime(unix_timestamp) FROM date;
+---------------------+---------------------+----------------+-------------------------------+
| date                | stamp               | unix_timestamp | from_unixtime(unix_timestamp) |
+---------------------+---------------------+----------------+-------------------------------+
| 2038-01-19 12:14:07 | 2038-01-19 12:14:07 |     2147483647 | 2038-01-19 12:14:07           |
+---------------------+---------------------+----------------+-------------------------------+
1 row in set (0.00 sec)


mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

// from_unixtime(unix_timestamp) の値はタイムゾーンの値になっている
mysql> SELECT *,from_unixtime(unix_timestamp) FROM date;
+---------------------+---------------------+----------------+-------------------------------+
| date                | stamp               | unix_timestamp | from_unixtime(unix_timestamp) |
+---------------------+---------------------+----------------+-------------------------------+
| 2038-01-19 12:14:07 | 2038-01-19 03:14:07 |     2147483647 | 2038-01-19 03:14:07           |
+---------------------+---------------------+----------------+-------------------------------+
1 row in set (0.00 sec)

結論

MySQLのタイムスタンプ型は2038年問題が起きるので、利用は控えていった方が良さそう。
タイムゾーンの変更ない場合は、DATETIME型でも良さそうだが、絶対値とするならUNIXタイムスタンプの値を保持したほうが良さそう。

補足

MySQL :: MySQL 8.0 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Typesを読む限り、MySQLを8.0にアップデートしたところで、この問題は解決しないようだ。
(MySQL側で対応してくれたらいいのな…)

参考