PostgreSQL の TIMESTAMPTZ 型について誤解していた部分があったので記事にします。以下の記事を参考にさせていただきました。
この記事で伝えたいこと
- TIMESTAMPTZ 型はタイムゾーンに関する情報を保持しない
- DB に設定されているタイムゾーンで挙動がかわってしまう操作があるので注意が必要
TIMESTAMPTZ 型はタイムゾーンに関する情報を保持しない
TIMESTAMPTZ 型は、timestamp with time zone という名前にも関わらず、タイムゾーンの情報を持たない。
実験:
タイムゾーンの異なる、同じ日時の値を SELECT してみる → SELECT して表示される日時のタイムゾーンは同じ
SELECT '2022-07-31 12:53:00+00'::TIMESTAMPTZ; -- 2022-07-31 21:53:00+09
SELECT '2022-07-31 21:53:00+09'::TIMESTAMPTZ; -- 2022-07-31 21:53:00+09
「日」で切り詰めてみる → 切り詰められた日時は同じ
SELECT date_trunc('day', '2022-07-31 12:53:00+00'::TIMESTAMPTZ); -- 2022-07-31 00:00:00+09
SELECT date_trunc('day', '2022-07-31 21:53:00+09'::TIMESTAMPTZ); -- 2022-07-31 00:00:00+09
TIMESTAMPTZ 型は単に UTC 時間だけを保持するようなものだと思われる。
DB に設定されたタイムゾーンで挙動がかわる操作
たとえば、以下のような場合に挙動の違いがありそう:
- DB のタイムゾーンによって挙動が変わる関数を利用するとき
- タイムゾーンコードを渡せる関数にタイムゾーンコードを渡さずデフォルトのタイムゾーンを利用しているとき
- TIMESTAMP 型を受け取る関数に TIMESTAMPTZ 型の値を渡すとき
- TIMESTAMP 型の値を TIMESTAMPTZ にキャストするとき
以下のような関数を利用するとき、意図しないタイムゾーンの変換をしてしまっているかもしれない。
-
date_trunc（日や月で切り詰めるとき、など） -
to_char（文字列に変換するとき、など） -
to_timestamp（文字列から変換するとき、など）
挙動が変わってしまう例
以下のような書き方のコードだと、DB に設定されたタイムゾーンによって挙動が変わることがある。
SELECT date_trunc('day', now());
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ), 'YYYY/MM/DD');
SELECT to_timestamp('20220802', 'YYYYMMDD');
以下のような書き方をすると DB に設定されたタイムゾーンを気にしなくてよくなる。
SELECT date_trunc('day', now(), 'UTC');
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ) AT TIME ZONE 'UTC', 'YYYY/MM/DD');
SELECT to_timestamp('20220802', 'YYYYMMDD')::TIMESTAMP AT TIME ZONE 'UTC';
実験
DB のタイムゾーンによっては結果が変わる書き方:
SET TIME ZONE 'Asia/Tokyo';
SELECT date_trunc('day', now()); -- 2022-08-02 00:00:00+09
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ), 'YYYY/MM/DD'); -- 2022/08/03
SELECT to_timestamp('20220802', 'YYYYMMDD'); -- 2022-08-02 00:00:00+09
SET TIME ZONE 'UTC';
SELECT date_trunc('day', now()); -- 2022-08-02 00:00:00+00
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ), 'YYYY/MM/DD'); -- 2022/08/02
SELECT to_timestamp('20220802', 'YYYYMMDD'); -- 2022-08-02 00:00:00+00
タイムゾーンによらず同じ値を返す書き方:
SET TIME ZONE 'Asia/Tokyo';
SELECT date_trunc('day', now(), 'UTC'); -- 2022-08-02 09:00:00+09
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ) AT TIME ZONE 'UTC', 'YYYY/MM/DD'); -- 2022/08/02
SELECT to_timestamp('20220802', 'YYYYMMDD')::TIMESTAMP AT TIME ZONE 'UTC'; -- 2022-08-02 09:00:00+09
SET TIME ZONE 'UTC';
SELECT date_trunc('day', now(), 'UTC'); -- 2022-08-02 00:00:00+00
SELECT to_char(('2022-08-02 23:00:00+00'::TIMESTAMPTZ) AT TIME ZONE 'UTC', 'YYYY/MM/DD'); -- 2022/08/02
SELECT to_timestamp('20220802', 'YYYYMMDD')::TIMESTAMP AT TIME ZONE 'UTC'; -- 2022-08-02 00:00:00+00
以下のようになっていればこの記事の内容を気にしなくて済むかもしれない。
- 「DB のタイムゾーンは UTC にする」などの決まりがあって、それが開発メンバーに共有されている
- タイムゾーンが絡む操作はテストされた共通の関数を利用する