はじめに
恥ずかしながらtimezoneについて今まであまり考えることなく、「クライアントとサーバのタイムゾーンは合わせとけば、とりあえず大丈夫」ということだけでやってきました。
結果、タイムゾーンを使いたいときにどういう問題を気にしないといけないのか？というのが整理できていませんでした。
ってことで、整理しました。（嘘を書いていたら、ご指摘ください。＞timzoneに強い誰か）
本書で確認したこと
- timezoneの必要性について
- クライアントとサーバ間でtimezoneが異なる場合の挙動を確認（どういう問題が起きるか）
timezoneの必要性について
DBサーバと異なるtimezoneのクライアントからDBサーバが参照される可能性があるか？
timezone付きのデータ型を選択するかどうかはこの可能性の有無で決まります。
もしtimezoneが異なるクライアントからアクセスが無ければ、これ以上考えることはないです。「timestamp without time zone」を使用しましょう。
with/without time zoneでレコードサイズに変わりはない（どちらも8バイト）ですが、withの場合、HH:MM:SS+09のように表示されるため、ログ等に出力させる場合、無駄にサイズを増やすことになるので「いらないなら、つけない！」これに限ります。
DBサーバと異なるtimezoneのクライアントからアクセスがある場合
この場合、timestamp with time zoneを使用する必要があるでしょう。ただ、DBサーバ構築、APの設計・開発でtimezoneが考慮されたものになっていないと、せっかくのデータも有効に使われません。
まぁ無いとは思いますが、取得したtimestampを全てローカルの時刻として認識するような場合、データの不整合を生む可能性に繋がります。
手元で挙動の確認
timezone 一致
-- with/withou time zoneの定義のあるテーブル
CREATE TABLE foo (id int, t timestamp without time zone, ttz timestamp with time zone );
-- Clientの現在時刻（5/1 12：00 JSTとした場合の値）をtimezoneなしで挿入
INSERT INTO foo VALUES (1, '2019/5/1 12:00:00','2019/5/1 12:00:00');
-- Clientの現在時刻（5/1 12：00 JSTとした場合の値）をtimezoneありで挿入
INSERT INTO foo VALUES (2, '2019/5/1 12:00:00 JST'::TIMESTAMP WITH TIME ZONE,'2019/5/1 12:00:00 JST'::TIMESTAMP WITH TIME ZONE);
結果は以下のとおり。
postgres=# SELECT * FROM foo;
id | t | ttz
----+---------------------+------------------------
1 | 2019-05-01 12:00:00 | 2019-05-01 12:00:00+09
2 | 2019-05-01 12:00:00 | 2019-05-01 12:00:00+09
(2 rows)
- 問題点
- 特になし！
- timezoneがあろうとなかろうと時刻にズレは起きません。
- 特になし！
timezone 不一致
-- with/withou time zoneの定義のあるテーブル
CREATE TABLE bar (id int, t timestamp without time zone, ttz timestamp with time zone );
-- Clientの現在時刻（5/1 03：00 UTCとした場合の値）をtimezoneなしで挿入
INSERT INTO bar VALUES (1, '2019/5/1 03:00:00','2019/5/1 03:00:00');
-- Clientの現在時刻（5/1 03：00 UTCとした場合の値）をtimezoneありで挿入
INSERT INTO bar VALUES (2, '2019/5/1 03:00:00 UTC'::TIMESTAMP WITH TIME ZONE,'2019/5/1 03:00:00 UTC'::TIMESTAMP WITH TIME ZONE);
結果は以下のとおり。
postgres=# SELECT * FROM bar;
id | t | ttz
----+---------------------+------------------------
1 | 2019-05-01 03:00:00 | 2019-05-01 03:00:00+09
2 | 2019-05-01 12:00:00 | 2019-05-01 12:00:00+09
(2 rows)
- 問題点
- id=1
- クライアントの時刻 UTC 3:00 で登録しているつもりですが、実際にはDBサーバのTIMEZONEである JST の3:00として記録されている
- （つまりクライアントからのデータが9時間前のデータとして記録されているということ。）
- id=2
- id=1のような登録時の時刻ズレはない。
- ただ、クライアントが参照する際、t列（timestamp without time zone）を参照していると、クライアントには 3:00 がJSTなのかUTCなのか判断できない。
- id=1
というようなことが起きてしまい、ちゃんとtimezoneを意識した作りになっていないと、データがどこの時間で入れられたものか判断できなくなってしまいます。
おわりに
正直、これまではtimezoneは一致させておけ。ぐらいの認識でしたが、改めて考えてみるとちゃんとやっていないとひどいことになりそうなのがよくわかり、timezoneを一致させる重要性をよく理解できました。
まぁ、timezoneの異なる環境からアクセスのあるシステムに関わることはないので、特にこの情報が活用されることはないだろう。
参考にした資料
調査時に、以下の資料を参考にさせていただきました。ありがとうございます。