はじめに
先日業務でtimestamp with timezoneのカラムに時刻を投入する機会があり、timestamp with timezoneの扱い方について調べました。
認識していた内容と結論
当初下記の様に認識（想像）していましたがこれは誤りです。
- データ投入時のtimezoneを持ったままDBへ保存されているのではないか
正しくはこちらでした。
-
データ投入時のtimezoneを持ったままDBへ保存されているのではないか→ 投入時に指定した日付データをUTC時刻に変換した上でデータの投入がされる
まずDBにデータを投入する際には、投入対象となるデータをUTC時刻に変換した上でデータの投入がされるため、DBに入っている値は恐らくyyyy-mm-dd hh : mm :ss+00になります。
こちらのデータを取得する際には、セッションやDBユーザに設定されているタイムゾーンを基に、取得結果データの時刻を変換してくれるというのがtimestamp with timezoneなんだと理解しました。
例
実際にデータがどうなるか下記で確認します
まずテーブルを作成します。
postgres=# create table test_timezone(tz_test timestamptz);
①セッションのタイムゾーンをUTCにしてデータを投入した場合
-- セッションタイムゾーンをUTCに変更
postgres=# SET SESSION TIMEZONE = UTC;
SET
-- データを挿入する
postgres=# INSERT INTO test_timezone VALUES('2024-07-07 00:00:00');
INSERT 0 1
-- 取得
postgres=# SELECT tz_test FROM test_timzone
-- セッションタイムゾーンがUTCの場合の取得結果
2024-07-07 00:00:00+00
-- セッションタイムゾーンをJSTに変更
postgres=# SET SESSION TIMEZONE = 'Asia/Tokyo';
SET
-- 先ほどと同様のデータを取得
postgres=# SELECT tz_test FROM test_timzone
-- 結果
2024-07-07 09:00:00+09
セッションのタイムゾーンが取得結果に適用されることを確認できました。投入時のタイムゾーンをUTCにしていたため、データもUTCで挿入されています。
②セッションのタイムゾーンJSTにしてデータを投入した場合
-- セッションタイムゾーンをJSTに変更
postgres=# SET SESSION TIMEZONE = 'Asia/Tokyo';
SET
-- データを挿入する
postgres=# INSERT INTO test_timezone VALUES('2024-07-07 00:00:00');
INSERT 0 1
-- 取得
postgres=# SELECT tz_test FROM test_timzone
-- セッションタイムゾーンがJSTの場合の取得結果
2024-07-07 00:00:00+09
-- セッションタイムゾーンをUTCに変更
postgres=# SET SESSION TIMEZONE = UTC;
SET
-- 先ほどと同様のデータを取得
postgres=# SELECT tz_test FROM test_timzone
-- 結果
2024-07-06 15:00:00+00
セッションタイムゾーンをJSTにした上でデータを投入すると、データがUTCに変換されて格納されていることが確認できました。
終わりに
なんとなく苦手意識がありましたが下記だけ気をつければ大丈夫かなと思っています
- 投入時にはUTCへ変換されるため、投入時に適用されているタイムゾーンに注意
- 取得時にはセッションやユーザ、DBのタイムゾーンが適用され、取得結果にそのタイムゾーンが適用される
今後、ユーザ、DB、セッションに設定されるタイムゾーンの優先順位も調べたいと思いました。