MySQL8でtimestamp型とdatetime型を比較する

突然ですがtimestamp型は以下の問題を抱えているのでdatetime使ったほうが無難、みたいな思考になったとき
「すでにtimestampでいくつもテーブルが存在するがあとからdatetimeのテーブルを追加してうまく機能します？」
という疑問があると思います。

基本的に取得する場合は差異がないはずですがJOINしてカラムを比較した場合はどうなるの？タイムゾーンによる影響はどうなの？
が不明瞭だったので実際に動かして実験してみます。

それではそれぞれのテーブルを作っておきます。

CREATE TABLE timestamp_table
(
    id bigint unsigned auto_increment primary key,
    created_at timestamp not null
) COLLATE = utf8mb4_general_ci;

CREATE TABLE datetime_table
(
    id bigint unsigned auto_increment primary key,
    created_at datetime not null
) COLLATE = utf8mb4_general_ci;

データも入れておきます。

INSERT INTO timestamp_table (created_at) VALUES (now()), (now() - INTERVAL 1 DAY), (now() - INTERVAL 2 DAY);
INSERT INTO datetime_table (created_at) VALUES (now()), (now() - INTERVAL 1 DAY), (now() - INTERVAL 2 DAY);

中を見てると同じですね。

SELECT * FROM timestamp_table;

1,2021-10-20 03:28:46
2,2021-10-19 03:28:46
3,2021-10-18 03:28:46

SELECT * FROM datetime_table;

1,2021-10-20 03:28:46
2,2021-10-19 03:28:46
3,2021-10-18 03:28:46

created_atでJOINしてみましょう。

SELECT * FROM timestamp_table tt JOIN datetime_table dt on tt.created_at = dt.created_at;

1,2021-10-20 03:28:46,1,2021-10-20 03:28:46
2,2021-10-19 03:28:46,2,2021-10-19 03:28:46
3,2021-10-18 03:28:46,3,2021-10-18 03:28:46

予想通りJOINできました。
ということで疑問だった「JOINしてカラムを比較した場合はどうなるの？」は問題ないことが確認できました。

続いてタイムゾーンを変えてみましょう。現在のタイムゾーンは

SHOW VARIABLES LIKE '%time_zone%';

system_time_zone,UTC
time_zone,SYSTEM

となっているのでTokyo/Asiaに変更しましょう。

SET time_zone = 'Asia/Tokyo';

先程のJOINによるクエリを実行してみます。

SELECT * FROM timestamp_table tt JOIN datetime_table dt on tt.created_at = dt.created_at;

結果は0件。ONの条件が一致しなくなっています。
改めてtimestamp_tableとdatetime_tableを見比べてみましょう。

SELECT * FROM timestamp_table;

1,2021-10-20 12:28:46
2,2021-10-19 12:28:46
3,2021-10-18 12:28:46

SELECT * FROM datetime_table;

1,2021-10-20 03:28:46
2,2021-10-19 03:28:46
3,2021-10-18 03:28:46

timestamp_tableは+9時間になっています。当たり前なのですがtimestamp型ではタイムゾーンが考慮されますが、datetime型では考慮されないことがわかります。
「タイムゾーンによる影響はどうなの？」に関してはMySQLのタイムゾーン設定に影響があり、timestamp型とdatetime型のカラムをJOINや比較等をした場合に影響がある。ということがわかりました。

まとめ

• JOINしてカラムを比較した場合はどうなるの？
  • 特に影響はないと思われる
• タイムゾーンによる影響はどうなの？
  • 影響をうけるのでタイムゾーン設定を変更した場合は想定していたクエリの結果と異なる可能性がある

ちなみにLaravelのマイグレーションで利用されるBlueprintのtimestamps関数はもちろんtimestamp型でcreated_atとupdated_atを作るんですが、みなさん2038年問題に際してはどう考えてるのやら？
気になります