timestamp型とdatetime型
MySQLの日付・時刻表現にはtimestamp型とdatetime型がある。
MySQLのtimestamp型は内部表現に整数を用いていて、これが32bit精度の場合には、2038-01-19 03:14:07UTCまでしか表現できない。これは日本のタイムゾーンだと2038-01-19 12:14:07JSTに対応する。
一方datetime型は文字列で日時を表現していて、その範囲は1000年～9999年の間とされている。
timestamp型とdatetime型の有効範囲を比較すると以下のとおり。
| timestamp | datetime |
| UTC内部表現で保持 | タイムゾーン文字列で保持 |
| 1970-01-01 00:00:01UTC～2038-01-19 03:14:07UTC | 1000-01-01 00:00:00～9999-12-31 23:59:59 |
timestamp型の2038年問題
確認環境
timestamp型で上限値より大きな値を登録しようとすると、エラーで登録ができない。このことを確認してみる。環境は以下のとおり。
- Windows(64bit版)上のVagrant+VirtualBOXで構築したCentOS7
- MySQLのバージョンは8.0.26
|
1 2 |
$ mysql --version mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) |
確認用のテーブルは以下のとおり。
|
1 2 3 4 5 6 7 8 |
mysql> DESCRIBE test; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | int | YES | | NULL | | | time | timestamp | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
上限を超える値の登録は不可
まず上限値一杯の日時を登録してみる。UTCに対して日本のタイムゾーンで表現した2038年1月19日12時14分07秒を登録すると、問題なく登録される。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:07'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM test; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2038-01-19 12:14:07 | +------+---------------------+ 1 row in set (0.00 sec) |
次に上限値に1秒を加えた日時を登録しようとすると、"Incorrect datetime value"となって登録できない。
|
1 2 |
mysql> INSERT INTO test VALUES(1, '2038-01-19 12:14:08'); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 12:14:08' for column 'time' at row 1 |
timestamp型に対する加算の場合は9999年まで
次に登録された上限一杯の値に対してインターバルを加えていき、問題が生じるまでその境界を探していった。
その結果、9999年12月31日23時59分59秒999999…までは登録可能だが、10000年1月1日0時0分0秒に達することはできず、その場合にはNULLとなることがわかった。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND FROM test; +---------------------------------------------------------+ | time + INTERVAL 60*60*24*365*7967.23695944317605 SECOND | +---------------------------------------------------------+ | 9999-12-31 23:59:59.999999 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND FROM test; +---------------------------------------------------------+ | time + INTERVAL 60*60*24*365*7967.23695944317606 SECOND | +---------------------------------------------------------+ | NULL | +---------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) |
32bitの上限を超えた後も値は保持されているが、その次の上限はdatetime型の上限と一致している。以下の何れかと推測される。
- 内部的には64bit表現だが、入力時には32bitの上限で、演算時には
datetime型の上限で抑えている - 内部的に32bit表現だが、その上限を超えたときには
datetime型に内部的に切り替えている
まとめ
- MySQLの
timestamp型は、2038年の上限値を超える値を登録できない timestamp型に対する加算を行った場合、datetime型と同じ上限まで値を保持できる- 加算後に
datetime型の上限を超えた場合の値はNULLになる
- 加算後に
64bit整数の場合はtimestampも西暦3000億年弱まで扱い可能だが、一部が64bitシステムでも、データベース、言語、フレームワークの全てが対応していないとまずそうなので、日付時刻を扱う際はdatetime型としておくのが安全なようだ。