概要
MySQLのtimestamp型は2038年問題が発生すると聞きました。
自分たちは結構ガッツリtimestamp型を使っているので、動作を確認していきます。
対象はMySQL5.6です。
TL;DR
- timestamp型は2038年問題に直面する
- 対策としては、DATETIME型にするかINT型でUNIXタイムスタンプを管理するか
- 無難なのはUNIXタイムスタンプを管理する方
動作確認
まずは適当なテーブルを作成し、データを登録していきます。
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側で対応してくれたらいいのな…)