Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

概要

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側で対応してくれたらいいのな…)

参考

juve_534
駆け出しから5年ほど保守ばかりやっていたPHPer٩( 'ω' )و 今は新規開発でLaravelやAWSと戯れている。 ScalaやHack、Elixirが気になっている。
dmmcom
総合エンタテイメントサイト「DMM.com」を運営。会員数は2,900万人を突破。動画配信、FX、英会話、ゲーム、太陽光発電、3Dプリンタなど40以上のサービスを展開。沖縄での水族館事業参入、ベルギーでのサッカークラブ経営など、様々な事業を手掛ける。また2018年より若手起業家の支援を強化、「DMM VENTURES」による出資や、M&Aなどを積極的に展開している。
https://dmm-corp.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした