データベースを設計するにあたり日付というのはかなりデリケートな部分になります。
後述しますが、間もなくきたるべき時を迎え、諸問題が発生する可能性を秘めたデータベースも数多くあるはずです。
そこで今回は日付(DATETIME)について解説してみたいと思います。
DATETIMEとTIMESTAMP
日付を扱う上で日付に関するデータ型というのはいくつかありますが、よく似ているのがこの二つのデータ型です。
しかし実は似て非なるもので、いくつかの大きな違いがあります。
格納可能な範囲の違いについて
もう一つの大きな違いは格納できるデータの範囲です。
リファレンスマニュアルによると、DATETIMEのサポート有効範囲は”1000-01-01 00:00:00″から”9999-12-31 23:59:59″までとなっています。
これに対しTIMESTAMPでは”1970-01-01 00:00:01″UTC から”2038-01-19 03:14:07″UTCまでとなっています。
タイムゾーンについて
TIMESTAMPの値は、保存時にはタイムゾーンからUTCへ、読み出し時にはUTCからタイムゾーンへと変換されます。
これに対しDATETIMEはタイムゾーンの影響を受けることなく文字列として認識され保存されます。
また、読み出し時にも文字列認識により、そのままの状態で出力されます。
DATETIMEとTIMESTAMPの違いを実際に肌で知るために実際のコードで確かめてみましょう。
これに使用するテーブルは以下の通りです。
CREATE TABLE date_table( -> datetime_col_1 datetime, -> timestamp_col1 timestamp -> ); Query OK, 0 rows affected (0.028 sec) desc date_table; +----------------+-----------+------+-----+---------------------+-------------------------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-----------+------+-----+---------------------+-------------------------------+ | datetime_col_1 | datetime | YES | | NULL | | | timestamp_col_1 | timestamp | NO | | current_timestamp() | on update current_timestamp() | +----------------+-----------+------+-----+---------------------+-------------------------------+ 2 rows in set (0.019 sec)
次に、この予め用意したテーブルのレコードにそれぞれ現在時刻を追加します。
INSERT INTO date_table (datetime_col_1, timestamp_col_1) VALUES (now(), now()); Query OK, 1 row affected (0.003 sec) SELECT datetime_col_1, timestamp_col_1 FROM date_table; +---------------------+---------------------+ | datetime_col_1 | timestamp_col_1 | +---------------------+---------------------+ | 2019-12-28 23:34:56 | 2019-12-28 23:34:56 | +---------------------+---------------------+ 1 row in set (0.000 sec)
ここまでは特に問題なくどちらも表示されています。
では次にタイムゾーンをUTCに変更してから再度同じクエリを実行してみましょう。
SET TIME_ZONE = '+00:00'; Query OK, 0 rows affected (0.000 sec) SELECT datetime_col_1, timestamp_col_1 FROM date_table; +---------------------+---------------------+ | datetime_col_1 | timestamp_col_1 | +---------------------+---------------------+ | 2019-12-28 23:34:56 | 2019-12-28 14:34:56 | +---------------------+---------------------+ 1 row in set (0.000 sec)
先ほど説明したようにTIMESTAMP型はJST→UTCに変換されて表示されているのに対し、DATETIME型は文字列として保存されるためUTC変換が効いていないことが確認できます。
デフォルト値について
明示的に指定しない場合のデフォルト値にも違いがあります。
DATETIMEのデフォルト値は”NULL”ですが、TIMESTAMPのデフォルト値は”0000-00-00 00:00:00″となっています。
2038年問題
システム開発に携わっている方なら間違いなく知っている大問題として「2038年問題」というのがあります。
これはTIMESTAMPのサポート範囲が問題となり重大なエラーを引き起こしてしまうというものです。
実際にどうなるのか、サンプルコードを作成しましたので見てみましょう。
CREATE TABLE sample_stamp(
-> id INT NOT NULL AUTO_INCREMENT,
-> _stamp TIMESTAMP,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.018 sec)
INSERT INTO sample_stamp (_stamp) VALUES ("2038-01-19 12:14:07");
Query OK, 1 row affected (0.001 sec)
INSERT INTO sample_stamp (_stamp) VALUES ("2038-01-19 12:14:08");
Query OK, 1 row affected, 1 warning (0.003 sec)
TIMESTAMPのサポート範囲は”2038-01-19 03:14:07UTCまで”となっており、これは日本標準時に置き換えると”2038-01-19 12:14:07″となります。
上のコードではこの範囲内と範囲外の時間を登録してあります。
それでは早速、これら二つの実行結果を見てみましょう。
SELECT * FROM sample_stamp; +----+---------------------+ | id | _stamp | +----+---------------------+ | 1 | 2038-01-19 12:14:07 | | 2 | 0000-00-00 00:00:00 | +----+---------------------+ 2 rows in set (0.000 sec)
サポート範囲内の方は特に問題なく登録できているのに対し、サポート範囲外の時間はデフォルト表示になっていることが確認できます。
TIMESTAMPには、不正な日時の設定がされた場合にはデフォルトに強制変換するという特徴があるためこのような結果となります。
このことから、今後の開発ではTIMESTAMP型を使うことは避け、DATETIME型を優先する方が賢明だと言えます。
まとめ
いかがでしたか？
今回はDATETIMEについてTIMESTAMPと併せて解説してみました。
2038年までアップデートされないまま稼働しているシステムはないと思いますが、だからと言って気にも留めないままにしておくとあっという間に問題の年が来てしまいます。
問題があるということを知るのと知らないのでは大きな違いになることもありますので、この記事で問題に気づいていただけたら嬉しいです。
UTCとJST
UTC(Universal Time, Coordinated)は協定世界時、JST(Japan Standard Time)は日本標準時を指します。
以前はよく聞いていたグリニッジ標準時(GMT)も今では協定世界時にその座を譲っているんです。