MySQLのDATETIME型とTIMESTAMP型のタイムゾーン的な違いの話+O/Rマッパーのタイムゾーンの挙動の話

経緯

WebアプリケーションのDBとしてMySQLを使っている中で、タイムゾーンが問題となる場面にでくわしたのですが、datetime型とtimestamp型のタイムゾーン的な違いを明確に説明できなかったので、その他のことも含めてこの二つの型の違いを調べてまとめました。

また、O/Rマッパー（ActiveRecordとSequelize）のタイムゾーン設定の挙動もまとめました。

tl:dr

すごく長くなりました。先にまとめを見た方が良いです。
=> まとめ

前置き

2018/02/01現在、5.7の日本語リファレンスは無いようです。この投稿での実行環境は5.7ですが、5.6と5.7に違いがないものは日本語の方（5.6のリファレンス）を引用します。

違いを説明する際に必要となるMySQLのこと

SQLモード関連

現在のSQLモードの確認

PaaSとか使うとSQLモードの設定は結構最初から色々ついていたり、あとは意識していないとローカルと本番で設定が違って挙動が変わるみたいなのもありえるので、SQLモードは確認しておきたいですね。

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

SQLモードの変更

mysql> SET GLOBAL sql_mode = 'modes';

# その接続上のみの値を変更する場合は
mysql> SET SESSION sql_mode = 'modes';

もしくは、my.cnfに記述することで設定することも可能です。

my.cnf

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

厳密モード

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

このマニュアルの「厳密モード」とは、STRICT_TRANS_TABLES または STRICT_ALL_TABLES のいずれかあるいは両方が有効なモードを意味します。

時間の型を扱う時に知っておきたいこと

型の種類

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型
参考：MySQL :: MySQL 5.7 Reference Manual :: 11.3 Date and Time Types

MySQLにおいて時間を扱う型（便宜上以降は「時間の型」と呼びます）の種類は以下の通りです。

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

うーんYEARなんてあったのか。

時間の型における「ゼロ」

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

デフォルトで MySQL は、日付または時間型の値で、範囲外であるか、それ以外で型にとって無効である値を見つけた場合、値をその型の「ゼロ」値に変換します。その例外では、範囲外の TIME 値は TIME 範囲の適切な終点に切り落とされます。

各型における「ゼロ」の値は、各型の形式のすべての値を0にしたものですね。
実際の値はリファレンスマニュアルに表でまとまっています。

NO_ZERO_DATEとNO_ZERO_IN_DATE

SQLモードの中にNO_ZERO_DATEとNO_ZERO_IN_DATEという二つの値があって、それぞれ時間の型にとってのゼロの値をどう扱うかの設定を担っています。

• NO_ZERO_DATE：時間の型に「ゼロ」の値が入ると警告を出す。
  • そのカラムの型にとっての「ゼロの値」：00-00-00 00:00:00や00-00-00など。
  • 厳密モードが有効な場合はエラー。
• NO_ZERO_IN_DATE：時間の型の値に一部0が入ると警告を出す。
  • 2009-00-00や2009-01-00など。
  • 厳密モードが有効な場合はエラー。

デフォルトでは、MySQLはゼロの値を許容するようになっていますが、用途的に弾きたい場合は上の二つを設定することになります。

が、NO_ZERO_DATEはMySQL5.6.17以降で非推奨になりました。
参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

MySQL 5.6.17 以降では、NO_ZERO_DATE は非推奨となり、これを含むように sql_mode 値を設定すると警告が生成されます。MySQL 5.7 では、このモードは何も行いません。その代わり、この効果は厳密モードの効果に含められています。

参考：MySQL :: MySQL 5.7 Reference Manual :: 5.1.8 Server SQL Modes

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

5.6.17以降ではNO_ZERO_DATEは厳密モードに含まれるようです。

タイムゾーンの設定

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.6 MySQL Server でのタイムゾーンのサポート
参考：MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support

MySQLのタイムゾーンの設定は複数存在します。
リファレンスで使われている「サーバ(server)」という単語は「MySQLサーバ」のことですね。MySQLサーバが走るマシンのことは「ホストマシン(host machine)」と呼称されています。

• システムタイムゾーン
  • システム変数system_time_zoneのこと。
  • ホストマシンのタイムゾーンを示します。実際にホストマシンのタイムゾーンを参照するのはMySQLサーバが起動したときのみです。
• サーバタイムゾーン
  • システム変数time_zoneのこと。
  • MySQLサーバのタイムゾーンです。初期値はSYSTEMで、それはtime_zoneの値として現在のsystem_time_zoneを使用することを示します。
  • my.cnfで指定するdefault-time-zoneはこの値のことを指します。
  • コマンドではmysql> SET GLOBAL time_zone = timezone;で設定できます。

リファレンスにはこれに加えて３種類目の「接続ごとのタイムゾーン」が存在すると書いてあるんですが、これはシステム変数time_zoneのスコープが「グローバル、セッション」であるので接続ごとにタイムゾーンを変えられるよ！という意味ですね。
これはタイムゾーンに限った話ではないので、基本的にはsystem_time_zoneとtime_zoneの値がMySQLにおけるタイムゾーンの設定ということになります。
そして、実際に値を操作するときに参照する変数はtime_zoneであるため、time_zone がMySQLのタイムゾーンの設定で重要であるということになります。

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
参考：MySQL :: MySQL 5.7 Reference Manual :: 5.1.5 Server System Variables

DATETIME型とTIMESTAMP型の違い

本題です。

格納できるデータの範囲

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。MySQL では、DATETIME 値の取得と表示は 'YYYY-MM-DD HH:MM:SS' 形式で行われます。サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

TIMESTAMP データ型は、日付と時間の両方の部分を含む値に使用されます。TIMESTAMP には、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC の範囲があります。

イメージ的には

• datetimeは時間形式の情報を格納する型
• timestampは現実でのある一点を表す情報を格納する型

という感じでしょうか。

NULL制約とデフォルト値

明示的に指定しない場合のNULL制約とデフォルト値は、以下の通りです。

	datetime	timestamp
NULL制約	NULL	NOT NULL
デフォルト値	NULL	0000-00-00 00:00:00

タイムゾーン設定の影響

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.6 MySQL Server でのタイムゾーンのサポート

TIMESTAMP カラムの値は、ストレージでは現在のタイムゾーンから UTC に、読み出しでは UTC から現在のタイムゾーンに変換されます。

現在のタイムゾーン設定は、UTC_TIMESTAMP() 関数などによって表示される値、または DATE、TIME、DATETIME カラムの値には影響しません。また、これらのデータ型の値も UTC で格納されません。

リファレンスではさらっと書かれているんですが、違いを理解する上ですごく重要なポイントですね。

イメージとしては、

• datetime

  • INSERT時
    • 与えられた日付時刻リテラルを、time_zoneに関係なく文字列としてそのまま保存する。
  • SELECT時
    • 保存した文字列をそのまま渡す。

• timestamp

  • INSERT時
    • 与えられた日付時刻リテラルを、time_zoneで指定されたタイムゾーンでの時刻であると解釈し、それをUTC時刻に変更して保存する。
  • SELECT時
    • 保存したUTC時刻をtime_zoneで指定されたタイムゾーンでの表記に変更して渡す。

という感じです。

自動初期化機能と自動更新機能

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能
参考：MySQL :: MySQL 5.7 Reference Manual :: 11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME

datetimeとtimestampには自動初期化と自動更新という機能が存在しますが、それぞれで挙動が異なる部分があります。
これらの機能は5.6.5以前と以降で挙動が違うのでそこも要注意です。

• 自動初期化：レコードを生成する際にそのカラムの値が指定されない場合、自動で現在時刻を挿入する。
• 自動更新：レコードを更新する際にそのカラムの値が指定されない場合、自動で現在時刻に更新する。

二つの型で異なるのは主に、明示的に指定しない場合の設定です。

• datetime: 明示的に指定しない場合、自動初期化、自動更新は設定されない。
• timestamp: 明示的に指定しない場合、テーブル内の最初のtimestamp型カラムのみ自動初期化、自動更新が設定される。

このtimestamp型の挙動は、システム変数explicit_defaults_for_timestampによって変更することができます。

実際にやってみた

上記の違いを実際にMySQLのCLIで見てみます。

試行環境

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.13.2
BuildVersion:   17C205

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.20, for osx10.13 (x86_64) using  EditLine wrapper

# SQLモードの確認
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# タイムゾーン設定の確認
mysql> SELECT @@global.system_time_zone, @@global.time_zone, @@session.time_zone;
+---------------------------+--------------------+---------------------+
| @@global.system_time_zone | @@global.time_zone | @@session.time_zone |
+---------------------------+--------------------+---------------------+
| JST                       | SYSTEM             | SYSTEM              |
+---------------------------+--------------------+---------------------+
1 row in set (0.00 sec)

テーブルの作成

とりあえず型だけを指定したカラムを持つテーブルを作ってみます。

mysql> create table `table_1` (
  datetime_column_1 datetime,
  datetime_column_2 datetime,
  timestamp_column_1 timestamp,
  timestamp_column_2 timestamp,
  timestamp_column_3 timestamp
);

mysql> desc table_1;
+--------------------+-----------+------+-----+---------------------+-----------------------------+
| Field              | Type      | Null | Key | Default             | Extra                       |
+--------------------+-----------+------+-----+---------------------+-----------------------------+
| datetime_column_1  | datetime  | YES  |     | NULL                |                             |
| datetime_column_2  | datetime  | YES  |     | NULL                |                             |
| timestamp_column_1 | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| timestamp_column_2 | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| timestamp_column_3 | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+--------------------+-----------+------+-----+---------------------+-----------------------------+

それぞれのカラム作成のクエリには型の指定しかしてないですが、この時点で既に違いがありますね。

NULL制約とデフォルト値がそれぞれの型で異なっています。

テーブル内の最初のtimestamp型カラムにはDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPが付いていますね。これは自動初期化機能と自動更新機能のことを指しています。

• DEFAULT CURRENT_TIMESTAMP：自動初期化のこと。
• ON UPDATE CURRENT_TIMESTAMP：自動更新のこと。

データ挿入

タイムゾーン設定の影響を確認してみます。

現在時刻をdatetime、timestampそれぞれに挿入し、タイムゾーンを変更してみます。

# データ挿入
mysql> insert into table_1 (datetime_column_1, timestamp_column_2) values (now(), now());
Query OK, 1 row affected (0.01 sec)

# タイムゾーンがJSTでの値の取得
mysql> select datetime_column_1, timestamp_column_2 from table_1;
+---------------------+---------------------+
| datetime_column_1   | timestamp_column_2  |
+---------------------+---------------------+
| 2018-02-01 16:08:51 | 2018-02-01 16:08:51 |
+---------------------+---------------------+
1 row in set (0.00 sec)

# タイムゾーン設定の変更
mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

# タイムゾーンがUTCでの値の取得
mysql>  select datetime_column_1, timestamp_column_2 from table_1;
+---------------------+---------------------+
| datetime_column_1   | timestamp_column_2  |
+---------------------+---------------------+
| 2018-02-01 16:08:51 | 2018-02-01 07:08:51 |
+---------------------+---------------------+
1 row in set (0.00 sec)

比較してわかるように、タイムゾーンをUTCに変更した後に、timestamp型の値のみ表示が変わりました。

具体的には、

• JST -> UTC

の変更で、

• 2018-02-01 16:08:51 -> 2018-02-01 07:08:51

に変わりました。

JSTはUTC+09:00なので、JSTで保存された時間をUTCで見ると-09:00されます。
2018-02-01 16:08:51 - 09:00 = 2018-02-01 07:08:51ということですね。

タイムゾーン設定の影響でのイメージ通りに当てはめると、
timestamp型はtime_zoneがJST（+09:00）である時に2018-02-01 16:08:51がINSERTされた場合、2018-02-01 16:08:51をJST時刻であると解釈、内部ではそれをUTC時刻に変換した2018-02-01 07:08:51を保存する、ということになります。

そしてSELECTされた時にはその時点でのtime_zoneを確認し、それがJSTであれば、UTC時刻である2018-02-01 07:08:51をJSTに変換した2018-02-01 16:08:51を表示し、UTCであればUTC時刻である2018-02-01 07:08:51をそのまま表示する、となります。

timestamp型はINSERT時のtime_zoneが重要

上記からわかるように、timestamp型では、INSERT時のtime_zoneの値によって、同じ値を入れても保存されるデータが異なります。

アプリケーションの用途によってdatetimeとtimestampを上手く使い分けたいですね。

O/Rマッパーの挙動

MySQLだけの話はこれでわかったのですが、実際にWebアプリケーションを作るときはだいたいO/Rマッパーを使うので、RailsのActiveRecordとNodeのSequelizeの挙動を見てみます。

ActiveRecord

環境

$ ruby -v
ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-darwin17]

$ rails -v
Rails 5.1.2

テーブル作成

マイグレーションファイル作成

$ rails g model first_model datetime_column_1:datetime datetime_column_2:datetime timestamp_column_1:timestamp timestamp_column_2:timestamp timestamp_column_3:timestamp

マイグレーションファイル

class CreateFirstModels < ActiveRecord::Migration[5.1]
  def change
    create_table :first_models do |t|
      t.datetime :datetime_column_1
      t.datetime :datetime_column_2
      t.timestamp :timestamp_column_1
      t.timestamp :timestamp_column_2
      t.timestamp :timestamp_column_3

      t.timestamps
    end
  end
end

マイグレーション実行

$ rake db:migrate

実際に発行されたSQL

CREATE TABLE `first_models` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `datetime_column_1` datetime,
  `datetime_column_2` datetime,
  `timestamp_column_1` timestamp NULL,
  `timestamp_column_2` timestamp NULL,
  `timestamp_column_3` timestamp NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB

実際にできたテーブル

mysql> desc first_models;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| id                 | bigint(20) | NO   | PRI | NULL    | auto_increment |
| datetime_column_1  | datetime   | YES  |     | NULL    |                |
| datetime_column_2  | datetime   | YES  |     | NULL    |                |
| timestamp_column_1 | timestamp  | YES  |     | NULL    |                |
| timestamp_column_2 | timestamp  | YES  |     | NULL    |                |
| timestamp_column_3 | timestamp  | YES  |     | NULL    |                |
| created_at         | datetime   | NO   |     | NULL    |                |
| updated_at         | datetime   | NO   |     | NULL    |                |
+--------------------+------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

timestampのデフォルトの挙動を知っていると面白いですねーマイグレーションファイルには型の指定のみでオプションは何も指定してません。
しかし、実際に発行されているSQLを見ると、timestamp型にのみNULLオプションが付けられています。

これは、「timestamp型がデフォルトでNOT NULLになる」ことと、「Railsのマイグレーションファイル内でnull: false/trueを指定できる」ことの整合性を保つための差分吸収の処理ですね。

これで、「Railsのdatetimeやtimestampでは明示的にnull: falseを指定しない限り、カラムはNULLを許容する」と考えれば良いことになります。（datetimeやtimestamp型以外の型については明確に確認してませんが。）

ありがたや〜

Railsのタイムゾーンの罠

ちなみに、updated_atなんかはMySQLの自動初期化、自動更新と同じ役割だと思いますが、DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPは付与してないので、ActiveRecordが自動で現在時刻を日付リテラルで挿入していることになります。
この辺はRailsのタイムゾーンの設定がめんどくさそう面白そうじゃないですか。
RailsのデフォルトのタイムゾーンはUTCです。

ここで、MySQLのtime_zoneはJST、Railsはタイムゾーンの設定を一切変更していない状態です。

設定を変更せずにcreate

> FirstModel.create(timestamp_column_1: Time.current.to_s(:db))
# 〜省略〜
  SQL (0.4ms)  INSERT INTO `first_models` (`timestamp_column_1`, `created_at`, `updated_at`) VALUES ('2018-02-01 08:37:57', '2018-02-01 08:37:57', '22018-02-01 08:37:57')
# 〜省略〜
 timestamp_column_1: Thu, 01 Feb 2018 08:37:57 UTC +00:00,
# 〜省略〜
 created_at: Thu, 01 Feb 2018 08:37:57 UTC +00:00,
 updated_at: Thu, 01 Feb 2018 08:37:57 UTC +00:00>

作成したレコード

mysql> select timestamp_column_1, created_at, updated_at from first_models;
+---------------------+---------------------+---------------------+
| timestamp_column_1  | created_at          | updated_at          |
+---------------------+---------------------+---------------------+
| 2018-02-01 08:37:57 | 2018-02-01 08:37:57 | 2018-02-01 08:37:57 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

このレコード↑は日本時間の17:37にcreateをしたものです。UTC時刻になってますね。
ただActiveRecordによってパースされた値（createメソッドの返り値）もちゃんとUTC時刻になっているので、表示方法を変えれば問題はなさそうに見えます。

時間の表示に使うタイムゾーンの設定はRails.application.config.time_zoneの値です。

デフォルトの設定（日本時間の17時42分に実行）

> Rails.application.config.time_zone
=> "UTC"
> Time.current
=> Thu, 01 Feb 2018 08:42:08 UTC +00:00

config.time_zoneを変更すると、ActiveSupportのメソッドのタイムゾーンが変わります。

application.rb

# 〜省略〜
module TimeTypeTest
  class Application < Rails::Application
# 〜省略〜

    config.time_zone = "Tokyo" # <- 追加

# 〜省略〜
  end
end

日本時間の17時44分に実行

> Rails.application.config.time_zone
=> "Tokyo"
> Time.current
=> Thu, 01 Feb 2018 17:44:17 JST +09:00

これで先ほど作成したレコードを取得すると...

> FirstModel.last
# 〜省略〜
 timestamp_column_1: Thu, 01 Feb 2018 17:42:08 JST +09:00,
# 〜省略〜
 created_at: Thu, 01 Feb 2018 17:42:08 JST +09:00,
 updated_at: Thu, 01 Feb 2018 17:42:08 JST +09:00>

日本時間の17:42になってる、オーケー！と思いがちなんですが...
待てよ、DBのtime_zoneはJSTにしてるよな...と考え直すと、間違いが見えてきます。
セッション変数のtime_zoneを変更するとわかりやすいです。

mysql> set time_zone = "+00:00";
Query OK, 0 rows affected (0.00 sec)
mysql> select timestamp_column_1, created_at, updated_at from first_models;
+---------------------+---------------------+---------------------+
| timestamp_column_1  | created_at          | updated_at          |
+---------------------+---------------------+---------------------+
| 2018-02-01 03:37:57 | 2018-02-01 08:37:57 | 2018-02-01 08:37:57 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

がーん、timestampの方、time_zoneをUTCにしたら朝の３時になってます。timestamp型の特徴を思い出せば、当然ですよね。

要はこの場合MySQLは、time_zoneがJSTなのでtimestamp型で受け取った値をJSTだと思って保存します。しかしRailsアプリケーションはデフォルトの設定のままなのでDBのタイムゾーンはUTCだと思っており、DBから返ってくる値（JST時刻）をUTCとして表示します。

こうした、プログラム同士が互いに勘違いしているのに結果的に辻褄が合ってしまう状態のバグのことを、俗に「アンジャッシュバグ」と呼びたいです（希望）。

DBのtime_zoneを途中で変えない限りこのアプリケーション上では整合性が保たれ（ているように見え）ますが、実はDBに格納されているデータは間違ったものですし、他のプロセスやアプリケーションがそのDBのデータを使う際に問題が発生する可能性が高いです。

ここで重要なのはconfig.active_record.default_timezoneの値です。
ActiveRecordは、DBから返って来た時刻形式の文字列を、このdefault_timezoneのタイムゾーンの時刻だと解釈してRubyのオブジェクトにパースします。
これはデフォルトで:utcです。設定できる値は:utcか:localの２種類。
参考：Configuring Rails Applications — Ruby on Rails Guides
:localにすると、プロセスが走るホストマシンのタイムゾーンになります。

このconfig.active_record.default_timezoneの値がMySQLのtime_zoneと異なる値である場合、データに致命的な不整合が生じます。
しかも表示上では気付きにくいというトラップ付き。

application.rb

config.active_record.default_timezone = :local # <- 追加

↑変更します。（僕のマシンのタイムゾーンはJSTです。）
この状態でcreateしてみましょう。

日本時間の17時52分に実行

> FirstModel.create(timestamp_column_1: Time.current.to_s(:db))
# 〜省略〜
  SQL (0.3ms)  INSERT INTO `first_models` (`timestamp_column_1`, `created_at`, `updated_at`) VALUES ('2018-02-01 17:52:13', '2018-02-01 17:52:13', '2018-02-01 17:52:13')
# 〜省略〜
 timestamp_column_1: Thu, 01 Feb 2018 08:52:13 UTC +00:00,
# 〜省略〜
 created_at: Thu, 01 Feb 2018 08:52:13 UTC +00:00,
 updated_at: Thu, 01 Feb 2018 08:52:13 UTC +00:00>

mysql> select timestamp_column_1, created_at, updated_at from first_models;
+---------------------+---------------------+---------------------+
| timestamp_column_1  | created_at          | updated_at          |
+---------------------+---------------------+---------------------+
| 2018-02-01 17:52:13 | 2018-02-01 17:52:13 | 2018-02-01 17:52:13 |
+---------------------+---------------------+---------------------+
mysql> set time_zone = "+00:00";
Query OK, 0 rows affected (0.00 sec)
mysql> select timestamp_column_1, created_at, updated_at from first_models;
+---------------------+---------------------+---------------------+
| timestamp_column_1  | created_at          | updated_at          |
+---------------------+---------------------+---------------------+
| 2018-02-01 08:52:13 | 2018-02-01 17:52:13 | 2018-02-01 17:52:13 |
+---------------------+---------------------+---------------------+

今度は上手く行ってますね！

config.active_record.default_timezoneの値と、MySQLのtime_zoneの値は合わせましょう。

Sequelize

環境

$ node -v
v9.3.0

$ npm -v
5.6.0

$ express time_type_test_express

$ npm install --save sequelize sequelize-cli mysql2

SequelizeでTIMESTAMP型を使う

SequelizeはデフォルトでTIMESTAMP型をサポートしていません。
ので、Sequelize.TIMESTAMPが存在しません。
今回はとくに深いことを考えずにtype:'TIMESTAMP'と文字列で指定します。

$ ./node_modules/.bin/sequelize model:create --name first_table --underscored --attributes datetime_column:date,timestamp_column:timestamp

migrations/マイグレーションファイル.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('first_tables', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      datetime_column: {
        type: Sequelize.DATE
      },
      timestamp_column: {
        // type: Sequelize.TIMESTAMP
        type: 'TIMESTAMP' // <- 修正
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('first_tables');
  }
};

マイグレーション実行

$ ./node_modules/.bin/sequelize db:migrate --env development

mysql> desc first_tables;
+------------------+-----------+------+-----+-------------------+-----------------------------+
| Field            | Type      | Null | Key | Default           | Extra                       |
+------------------+-----------+------+-----+-------------------+-----------------------------+
| id               | int(11)   | NO   | PRI | NULL              | auto_increment              |
| datetime_column  | datetime  | YES  |     | NULL              |                             |
| timestamp_column | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_at       | datetime  | NO   |     | NULL              |                             |
| updated_at       | datetime  | NO   |     | NULL              |                             |
+------------------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

テーブルが作成されました。timestampに関してはサポートが無いので、何のラップもされずに普通にSQL発行されていると思います。
なのでテーブルの最初のtimestamp型には自動初期化、自動更新の設定がついてますね。

とりあえず雑にapp.jsの中で'./models'をrequireして、createしてみます。

app.js

// 〜省略〜
var models = require('./models');
var FirstTable = models.first_table;
// 〜省略〜
FirstTable.create({timestamp_column: '2018-02-01 18:10:01'});
// 〜省略〜

日本時間の18時10分に実行

$ npm start
Executing (default): INSERT INTO `first_tables` (`id`,`timestamp_column`,`created_at`,`updated_at`) VALUES (DEFAULT,'2018-02-01 18:10:01','2018-02-01 09:10:41','2018-02-01 09:10:41');

mysql> select * from first_tables;
+----+-----------------+---------------------+---------------------+---------------------+
| id | datetime_column | timestamp_column    | created_at          | updated_at          |
+----+-----------------+---------------------+---------------------+---------------------+
|  1 | NULL            | 2018-02-02 03:10:01 | 2018-02-01 09:10:41 | 2018-02-01 09:10:41 |
+----+-----------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

ふむん...
timestamp_columnの値がコードで書いた時間+9時間になってます。
しかし発行されたINSERT文の中でtimestamp_columnに渡している値は正しい...

ここでドキュメントにこんな文言が。

参考：Sequelize | Sequelize | The node.js ORM for PostgreSQL, MySQL, SQLite and MSSQL API Document

The timezone is also used to SET TIMEZONE when connecting to the server, to ensure that the result of NOW, CURRENT_TIMESTAMP and other time related functions have in the right timezone.

どうやら毎コネクションごとにSET TIMEZONEしてるらしい。
ははあわかったぞ、こんな感じだ。

1. Sequelizeは何も設定しないとデフォルトのタイムゾーンは+00:00（UTC）。
2. Sequelizeはコネクションを張るときにSET TIMEZONEしていて、UTCがセットされる。
3. INSERT INTO first_tables (id,timestamp_column,created_at,updated_at) VALUES (DEFAULT,'2018-02-01 18:10:01','2018-02-01 09:10:41','2018-02-01 09:10:41');が実行される。
4. そのコネクションではtime_zoneがUTCであるため、'2018-02-01 18:10:01'はUTC時間で保存される。
5. コマンドmysqlでMySQLに接続すると、SET TIMEZONEしていないため普通にtime_zoneはJSTになる。
6. その接続でSELECTすると、先ほど保存した2018-02-01 18:10:01 UTC +00:00をJSTに変換した2018-02-02 03:10:01が表示される。

という感じですね。

これはSequelizeのタイムゾーンを設定すれば良いだけか。

config/config.js

// 〜省略〜
  "development": {
    "username": "root",
    "password": null,
    "database": "time_type_test_node_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "timezone": "+09:00" // <- 追加
  },
// 〜省略〜

$ npm start
Executing (default): INSERT INTO `first_tables` (`id`,`timestamp_column`,`created_at`,`updated_at`) VALUES (DEFAULT,'2018-02-01 18:20:01','2018-02-02 18:20:48','2018-02-02 18:20:48');

mysql> select * from first_tables order by id desc limit 1;
+----+-----------------+---------------------+---------------------+---------------------+
| id | datetime_column | timestamp_column    | created_at          | updated_at          |
+----+-----------------+---------------------+---------------------+---------------------+
|  2 | NULL            | 2018-02-01 18:10:01 | 2018-02-01 18:10:41 | 2018-02-01 18:10:41 |
+----+-----------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

# タイムゾーンをUTCに変更
mysql> set @@session.time_zone = "+00:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from first_tables order by id desc limit 1;
+----+-----------------+---------------------+---------------------+---------------------+
| id | datetime_column | timestamp_column    | created_at          | updated_at          |
+----+-----------------+---------------------+---------------------+---------------------+
|  2 | NULL            | 2018-02-01 09:10:01 | 2018-02-01 18:10:41 | 2018-02-01 18:10:41 |
+----+-----------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

できた！
UTCにしたときに正しい値になっています。

Sequelizeではnew Sequelize(config)するときのconfigでtimezone: +09:00な感じで、MySQLのtime_zoneと同じ値を指定しましょう。

まとめ

MySQLのDATETIMEとTIMESTAMPの違い

	DATETIME	TIMESTAMP
データ範囲	'1000-01-01 00:00:00' から '9999-12-31 23:59:59'	'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC
デフォルトのNULL制約	NULL	NOT NULL
デフォルトのデフォルト値	NULL	0000-00-00 00:00:00
システム変数time_zoneの影響	ない	ある
明示的に指定しない場合、自動初期化、自動更新は	設定されない	テーブル内の最初のtimestamp型カラムのみ自動初期化、自動更新が設定される

その他重要なこと

• MySQLのタイムゾーンで使用されるのはシステム変数time_zoneの値。
• Railsでは
  • config.active_record.default_timezoneの値が、DBから時刻を取り出すときに使われる。
  • config.time_zoneは表示するときのタイムゾーンを示す。
  • config.active_record.default_timezoneの値と、MySQLのtime_zoneの値は合わせる。
• Sequelizeでは
  • 毎コネクションごとにSET TIMEZONEしてる。
  • new Sequelize({timezone: '+09:00'})という風にタイムゾーンを設定し、その値はtime_zoneの値と合わせる。