はじめに
MySQLで日時情報を格納する際には、DATETIME型とTIMESTAMP型の2つが使用されるケースがほとんどかと思います。
また、MySQL5.7までとMySQL8.0以降では、デフォルト設定で制約とDEFAULT値を指定しなかった場合のTIMESTAMP型の挙動の違いがあり、MySQL5.7までのTIMESTAMP型では、特殊な動作をします。
今回は、DATETIME型とTIMESTAMP型の違いにも触れながら、TIMESTAMP型を使用する際には、知っておいた方がよいことを記載したいと思います。
データサイズ
MySQL 5.6.4 以降は、DATETIME型、TIMESTAMP型ともにマイクロ秒まで格納できるようになりましたので、それ以降のバージョンでデータサイズは見直しされています。
| データ型 | MySQL 5.6.3 までのバージョン | MySQL 5.6.4 以降のバージョン |
|---|---|---|
| DATETIME | 8 byte | 5byte + 小数秒精度分 |
| TIMESTAMP | 4 byte | 4byte + 小数秒精度分 |
| 小数秒精度 | 格納サイズ |
|---|---|
| 0 | 0 byte |
| 1 または 2 | 1 byte |
| 3 または 4 | 2 byte |
| 5 または 6 | 3 byte |
例えば、秒までの格納でよければ DATETIME と精度指定なしで定義し、ミリ秒までを格納したい場合は DATETIME(3) 、 マイクロ秒までを格納したい場合は DATETIME(6) のように定義します。
こちらの詳細については、以下のリファレンス(バージョン8.0)をご参照下さい。
格納できる日時範囲
| データ型 | 最小 | 最大 |
|---|---|---|
| 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以外のタイムゾーンでは、その時間差を考慮する必要があります。
例えば、JSTの場合は、上記に+9時間した値が日時範囲となります。
また、TIMESTAMP型で格納できる日時範囲は限られており、今やもうそんなに遠くない2038年問題の影響を受けてしまう為、これからテーブル設計をされる際は、特に理由がない限りはTIMESTAMP型の使用は避けた方がよいでしょう。
デフォルトでのNOT NULL制約及びDEFAULT値の設定
DATETIME型及びTIMESTAMP型を定義した際に、制約指定がなかった場合のカラムのNOT NULL制約及びDEFAULT値の設定は、以下のようになります。
| データ型 | NOT NULL制約 | DEFAULT値 |
|---|---|---|
| DATETIME | なし | NULL |
| TIMESTAMP (MySQL5.7まで) |
あり | テーブル内で1つ目の定義： CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP テーブル内で2つ目以降の定義： ’0000-00-00 00:00:00′ |
| TIMESTAMP (MySQL8.0以降) |
なし | NULL |
※TIMESTAMP型については、MySQLのパラメータで上記制約の変更が可能ですが、それについては後述します。
DATETIME型に関しては、シンプルに、特に制約を指定しなければ以下のようになります。
- NULLの挿入を許可する。
- 指定せずにレコードを挿入すると、NULLが設定される。
TIMESTAMP型に関しても、MySQL8.0以降のバージョンでは、DATETIMEと同様となります。
特筆する必要があるのは、MySQL5.7までのTIMESTAMP型です。
例えば、テーブルを作成し、2つのTIMESTAMP型のカラムを特に制約を指定しないで、定義します。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE t1 (id tinyint, ts1 timestamp, ts2 timestamp, PRIMARY KEY(id)); mysql> SHOW CREATE TABLE t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE t1 ( id tinyint(4) NOT NULL, ts1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ts2 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
TIMESTAMP型の2つのカラムには、NOT NULL 制約が付与されていることと、先に定義した ts1 のDEFAULT値に CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP が付与され、2つ目に定義した ts2 は 0000-00-00 00:00:00 が付与されるといった特殊な定義となります。
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP は、レコード挿入時に指定がなかった場合と、レコード更新時に指定がなかった場合は、現在時間を挿入するというDEFAULT値になります。
それでは、値を指定せずに、レコードを登録してみます。
|
1 2 3 4 5 6 7 |
mysql> INSERT INTO t1 (id) VALUES (1); mysql> SELECT * FROM t1; +----+---------------------+---------------------+ | id | ts1 | ts2 | +----+---------------------+---------------------+ | 1 | 2023-07-12 03:40:49 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ |
1つ目の定義となる ts1 には、INSERT時点の日時が、2つ目の定義となる ts2 には、0000-00-00 00:00:00 が登録されました。
その後、値を指定せずにレコードを更新してみます。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> UPDATE t1 SET id = 2 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM t1; +----+---------------------+---------------------+ | id | ts1 | ts2 | +----+---------------------+---------------------+ | 2 | 2023-07-12 03:41:01 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+ |
ts1 はUPDATE時の日時に更新されています。
また、MySQL 5.7 では、sql_mode がデフォルトのままだと STRICT_TRANS_TABLES と NO_ZERO_DATE が有効である為、2つ目以降のTIMESTAMP型を定義する際に、DEFAULT値を指定しないと、0000-00-00 00:00:00 が設定できない為、以下のようにテーブル定義の段階でエラーとなります。
|
1 2 |
mysql> CREATE TABLE t1 (id tinyint, ts1 timestamp, ts2 timestamp, PRIMARY KEY(id)); ERROR 1067 (42000): Invalid default value for 'ts2' |
（sql_modeに STRICT_TRANS_TABLES または STRICT_ALL_TABLES が有効かつ、 NO_ZERO_DATE を有効にした場合には、このエラーが発生します。また、NO_ZERO_DATE と NO_ZERO_IN_DATE は、MySQL5.7で非推奨となり、今後のバージョンで厳密モードとよばれる STRICT_TRANS_TABLES と STRICT_ALL_TABLES に含まれるようになると宣言されています。）
explicit_defaults_for_timestamp でのTIMESTAMP型の挙動の制御
上で記載したMySQL5.7までのTIMESTAMP型のデフォルトでのNOT NULL制約及びDEFAULT値の設定には、MySQL5.6で追加されたパラメータ explicit_defaults_for_timestamp の設定が大きく影響しています。
explicit_defaults_for_timestamp は、簡単に言うと、MySQL5.7までのTIMESTAMP型のNOT NULL制約の付与、DEFAULT値の付与を制御するものとなり、パラメータのデフォルト値が以下のようになっています。
| バージョン | explicit_defaults_for_timestamp のデフォルト値 |
|---|---|
| MySQL5.6 | OFF |
| MySQL5.7 | OFF |
| MySQL8.0 | ON |
見てお気付きになるかもしれませんが、このパラメータをOFFに設定している場合に、MySQL5.7までの制約指定しなかった場合の、NOT NULL制約が付与される、DEFAULT値にNULL以外の値が設定されるという特殊な動作となります。
ですので、MySQL5.6 及び MySQL5.7 でも、explicit_defaults_for_timestamp=ON を設定すれば、以下のように NOT NULL制約が付与されずに、DEFAULT値にNULLが設定されるようになります。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE t1 (id tinyint, ts1 timestamp, ts2 timestamp, PRIMARY KEY(id)); mysql> SHOW CREATE TABLE t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE t1 ( id tinyint(4) NOT NULL, ts1 timestamp NULL DEFAULT NULL, ts2 timestamp NULL DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
逆に、MySQL8.0 にバージョンアップする際に、今までの特殊な動きを引き継ぎたい場合は、 explicit_defaults_for_timestamp=OFF を設定し、かつ sql_mode の設定から NO_ZERO_DATE を外すことで、これまでと同じ挙動をさせることができますが、MySQLを起動する際に、特殊な動作を行う設定が非推奨である旨の警告メッセージが出力されますので、ご注意下さい。
|
1 |
[Warning] [MY-010086] [Server] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). |
TIMESTAMP型はタイムゾーンの設定の影響を受ける
MySQLでのデフォルトのタイムゾーンは、以下のように設定されており、OSのタイムゾーンを引き継ぐ形になっています。
| システムパラメータ | デフォルト値 |
|---|---|
| time_zone | SYSTEM |
| system_time_zone | OSのタイムゾーン |
MySQLのタイムゾーンの設定についての詳細については、以下のリファレンスをご参照下さい。
そして、TIMESTAMP型だけは、レコード挿入時に設定されているタイムゾーンからUTCに変更して保存するという動作になります。具体的には、以下のようになります。
-
データ格納時
格納する日時を現在設定されているタイムゾーンからUTCに変更して保存。 -
データ取得時
格納されているデータをUTCから、現在設定されているタイムゾーンに変更して返す。
詳細については、以下のリファレンスをご参照下さい。
つまりは、レコード登録時のタイムゾーンと、レコード参照時のタイムゾーンが違えば、参照時に日時情報が変更されて返されることになります。
具体的に見てみましょう。
DATETIME型のdt1とTIMESTAMP型のts1を定義して、それぞれ 2023-01-01 00:00:00 を登録します。
|
1 2 3 4 5 6 7 8 |
mysql> CREATE TABLE t1 (id tinyint, dt1 datetime NULL, ts1 timestamp NULL, PRIMARY KEY(id)); mysql> INSERT INTO t1 VALUES (1, '2023-01-01 00:00:00', '2023-01-01 00:00:00'); mysql> SELECT * FROM t1; +----+---------------------+---------------------+ | id | dt1 | ts1 | +----+---------------------+---------------------+ | 1 | 2023-01-01 00:00:00 | 2023-01-01 00:00:00 | +----+---------------------+---------------------+ |
TIMESTAMP型については登録した日時で参照できていますが、UTCに変更されて格納される為、内部的に格納される値はJSTの9時間前となる 2022-12-31 15:00:00 となっているはずです。
登録後に、タイムゾーンを OS設定のJST から UTC(+00:00) に変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SHOW VARIABLES LIKE '%zone'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | JST | | time_zone | SYSTEM | +------------------+--------+ mysql> SET time_zone = '+00:00'; mysql> SHOW VARIABLES LIKE '%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | JST | | time_zone | +00:00 | +------------------+--------+ |
タイムゾーンをUTCに変更した後に、再度、レコードを参照すると、DATETIME型は登録した日時そのままですが、TIMESTAMP型はUTCで格納されている値から現在のUTCのタイムゾーンに変更する必要がない為、2022-12-31 15:00:00 となってしまっています。
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM t1; +----+---------------------+---------------------+ | id | dt1 | ts1 | +----+---------------------+---------------------+ | 1 | 2023-01-01 00:00:00 | 2022-12-31 15:00:00 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) |
この動作は複数のタイムゾーンの日時を参照する必要がある場合や意図してタイムゾーンを変更して参照したい場合には便利なのですが、この仕様を理解されていない方には、「おかしなことになってる！」ってなっちゃう方もおられるかもしれません。
さらにこの動作は、稼働中にタイムゾーンを変更する場合のみの影響にあらず、取得したバックアップをタイムゾーンの異なるMySQLへロードする際にも影響しうるという点にご注意下さい。
例えば、mysqldumpでSQL形式でダンプした際には、先程、タイムゾーンがJSTで登録したTIMESTAMP型の 2023-01-01 00:00:00 は、ダンプしたファイルでは、内部的に保持しているUTCの日時でダンプされます。 加えてダンプファイルの先頭の方に SET TIME_ZONE='+00:00' が付与されています。
|
1 2 3 4 5 |
・・・略) /*!40103 SET TIME_ZONE='+00:00' */; ・・・略) INSERT INTO t1 VALUES (1,'2023-01-01 00:00:00','2022-12-31 15:00:00'); ・・・略) |
これは、mysqldump のデフォルトで有効になっているオプション –tz-utc により、内部的に保持しているUTCの日時をそのまま出力し、インポートする際は、UTCのタイムゾーンそのままで登録する為、タイムゾーンに違いがあっても元の日時で正常にロードできるようになっています。
ですが、mysqldumpの –tab オプションでTSV形式で取得するとした場合は、テーブルごとにレコード情報が出力される各ファイルには、SET TIME_ZONE='+00:00' が含まれません。
その為、インポートする際には、MySQLクライアントで接続する際に、--init-command="SET TIME_ZONE='+00:00'; を設定したりしないと、タイムゾーンの異なるサーバ間でのリストアでTIMESTAMP型のデータが、意図しない変換が行われているといった事が起きてしまいます。
まとめ
ここまで記載した内容で、以下の理由から、これから新たにMySQLのテーブル設計を行う際に、日時情報を格納する場合は、TIMESTAMP型より、DATETIME型を使用することを推奨します。
- 格納できる日時範囲が、UTCの
1970-01-01 00:00:01から2038-01-19 03:14:07まで。 - データ格納する際に、現在設定されているタイムゾーンから UTC への変換が生じる。
（タイムゾーンの変更等により、思わぬトラブルが発生する可能性がある。）
また、既にTIMESTAMP型を使用しているシステムにおいては、MySQL5.7以前からMySQL8.0へバージョンアップする際に、MySQLのパラメータのデフォルト設定では、TIMESTAMP型の制約、DEFAULT値が変更されますので、この辺の動作の違いも考慮しておきましょう。
