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 モード](https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html)

```mysql
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
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 モード](https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html)
>  このマニュアルの「厳密モード」とは、STRICT_TRANS_TABLES または STRICT_ALL_TABLES のいずれかあるいは両方が有効なモードを意味します。


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

## 型の種類

参考：[MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-types.html)
参考：[MySQL :: MySQL 5.7 Reference Manual :: 11.3 Date and Time Types](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html)

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

- DATE
- TIME
- DATETIME
- TIMESTAMP
- YEAR

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

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

参考：[MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-types.html)
> デフォルトで 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 モード](https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html)
>  MySQL 5.6.17 以降では、NO_ZERO_DATE は非推奨となり、これを含むように sql_mode 値を設定すると警告が生成されます。MySQL 5.7 では、このモードは何も行いません。その代わり、この効果は厳密モードの効果に含められています。 

参考：[MySQL :: MySQL 5.7 Reference Manual :: 5.1.8 Server SQL Modes](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html)
> 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 でのタイムゾーンのサポート](https://dev.mysql.com/doc/refman/5.6/ja/time-zone-support.html)
参考：[MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support](https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html)

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 サーバーシステム変数](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_time_zone)
参考：[MySQL :: MySQL 5.7 Reference Manual :: 5.1.5 Server System Variables](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_time_zone)

# DATETIME型とTIMESTAMP型の違い

本題です。

## 格納できるデータの範囲

参考：[MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型](https://dev.mysql.com/doc/refman/5.6/ja/datetime.html)
> 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 でのタイムゾーンのサポート](https://dev.mysql.com/doc/refman/5.6/ja/time-zone-support.html)
> 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 の自動初期化および更新機能](https://dev.mysql.com/doc/refman/5.6/ja/timestamp-initialization.html)
参考：[MySQL :: MySQL 5.7 Reference Manual :: 11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME](https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html)

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

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

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

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

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

# 実際にやってみた

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

## 試行環境

```console
$ 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
```

```mysql
# 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
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制約とデフォルト値](#null制約とデフォルト値)がそれぞれの型で異なっています。

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

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

## データ挿入

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

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

```mysql
# データ挿入
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

### 環境

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

$ rails -v
Rails 5.1.2
```

### テーブル作成

```console:マイグレーションファイル作成
$ 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
```

```ruby:マイグレーションファイル
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

```

```console:マイグレーション実行
$ rake db:migrate
```

```sql:実際に発行された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:実際にできたテーブル
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はタイムゾーンの設定を一切変更していない状態です。

```irb:設定を変更せずに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:作成したレコード
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`の値です。

```irb:デフォルトの設定（日本時間の17時42分に実行）
> Rails.application.config.time_zone
=> "UTC"
> Time.current
=> Thu, 01 Feb 2018 08:42:08 UTC +00:00
```

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

```ruby:application.rb
# 〜省略〜
module TimeTypeTest
  class Application < Rails::Application
# 〜省略〜

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

# 〜省略〜
  end
end

```

```irb:日本時間の17時44分に実行
> Rails.application.config.time_zone
=> "Tokyo"
> Time.current
=> Thu, 01 Feb 2018 17:44:17 JST +09:00
```

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

```irb
> 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
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](http://guides.rubyonrails.org/configuring.html#configuring-active-record)
`:local`にすると、プロセスが走るホストマシンのタイムゾーンになります。

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

```ruby:application.rb
config.active_record.default_timezone = :local # <- 追加
```

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

```irb:日本時間の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
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

### 環境

```console
$ 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'`と文字列で指定します。


```console
$ ./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');
  }
};
```

```console:マイグレーション実行
$ ./node_modules/.bin/sequelize db:migrate --env development
```

```mysql
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'});
// 〜省略〜
```

```console:日本時間の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
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](http://docs.sequelizejs.com/class/lib/sequelize.js~Sequelize.html)
> 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" // <- 追加
  },
// 〜省略〜
```

```console
$ 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
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`の値と合わせる。