MySQLのTIMESTAMP型を使用する前に知っておきたいこと

2023年7月14日2024年10月28日

はじめに

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)をご参照下さい。

11.7 データ型のストレージ要件

格納できる日時範囲

データ型	最小	最大
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型のカラムを特に制約を指定しないで、定義します。

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

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値になります。

それでは、値を指定せずに、レコードを登録してみます。

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 |
+----+---------------------+---------------------+

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 が登録されました。

その後、値を指定せずにレコードを更新してみます。

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 |
+----+---------------------+---------------------+

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 が設定できない為、以下のようにテーブル定義の段階でエラーとなります。

mysql> CREATE TABLE t1 (id tinyint, ts1 timestamp, ts2 timestamp, PRIMARY KEY(id));
ERROR 1067 (42000): Invalid default value for 'ts2'

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が設定されるようになります。

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

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を起動する際に、特殊な動作を行う設定が非推奨である旨の警告メッセージが出力されますので、ご注意下さい。

 [Warning] [MY-010086] [Server] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

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のタイムゾーンの設定についての詳細については、以下のリファレンスをご参照下さい。

5.1.15 MySQL Server でのタイムゾーンのサポート

そして、TIMESTAMP型だけは、レコード挿入時に設定されているタイムゾーンからUTCに変更して保存するという動作になります。具体的には、以下のようになります。

データ格納時
格納する日時を現在設定されているタイムゾーンからUTCに変更して保存。
データ取得時
格納されているデータをUTCから、現在設定されているタイムゾーンに変更して返す。

詳細については、以下のリファレンスをご参照下さい。

11.2.2 DATE、DATETIME、および TIMESTAMP 型

つまりは、レコード登録時のタイムゾーンと、レコード参照時のタイムゾーンが違えば、参照時に日時情報が変更されて返されることになります。

具体的に見てみましょう。
DATETIME型のdt1とTIMESTAMP型のts1を定義して、それぞれ 2023-01-01 00:00:00 を登録します。

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 |
+----+---------------------+---------------------+

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) に変更します。

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 |
+------------------+--------+

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 となってしまっています。

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> 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' が付与されています。

・・・略)
/*!40103 SET TIME_ZONE='+00:00' */;
・・・略)
INSERT INTO t1 VALUES (1,'2023-01-01 00:00:00','2022-12-31 15:00:00');
・・・略)

・・・略)

/*!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値が変更されますので、この辺の動作の違いも考慮しておきましょう。

MySQL運用・管理の課題はプロが解決します！

データベースのパフォーマンスが悪い、ダウンタイムが気になる、複雑な運用に苦戦している…そんなMySQLの運用・管理に関するお悩みはありませんか？

そんな方は、ぜひ当社のオープンソースDBサポートをご検討ください。

オラクル公式パートナーである当社の経験豊富なエンジニアが、MySQL / MariaDB / CassandraをはじめとしたOSSデータベースの導入から運用まで、あらゆる課題に対してハイレベルで迅速なサポートを提供します。システムのパフォーマンス改善や安定した運用を実現します。

初回のご相談・お見積りは無料です！
MySQL運用・管理にお悩みの方は、まずはお気軽にお問い合わせください。

→ 今すぐ無料相談を申し込む

よかったらシェアしてね！