timestamp with timezone と timestamp
PostgreSQLの日付時刻型をおさらいしましょう。
-
timestamp with timezone
絶対時刻を保持している型です。名前にはちょっと反して、タイムゾーン情報は持っていません。 -
timezone
正しくはtimestamp without timezone。
どこのタイムゾーンのものとも限らない年月日時分秒を保持している型です。タイムゾーンが決まっていないゆえ、いつの瞬間を指しているとは定まりません。
timestamp with timezone ⇔ timestamp の相互変換
SQLを書いていれば、timestamp with timezone型とtimestamp型のカラムを比較しないといけないシーンが出てくることがあるかもしれません。もしくは、取得した後のアプリロジック側の都合で絶対時刻であるか日本時間時刻であるか決まっていてそれにあわせに行く必要があるかもしれません。そういうときに型変換が発生します。
たとえばDBには日本時間の日時がtimestamp型で入っているとしますが、それを読み出すアプリでは絶対時刻値が必要というケース
SELECT
cast(some_timestamp as timestamp with timezone) as some_timestamptz,
...
このようにcast構文を使うと一見正しく変換できて動いてくれてしまいます。
「日本時間で」という情報がどこにも与えられていませんが、PostgreSQLは何かの設定からこの文脈は日本時間であるということを知って、ちゃんと日本時間日付時刻を読み取って絶対時刻にしてくれます。つまりデフォルトタイムゾーンがあるってことですね。
そしてこの「何かの設定から」が非常にハイリスクなのです。
これはちょうど10年前、PostgreSQLが9.1から9.2にバージョンアップしたときにconfファイルにtimezoneが未設定だったときに設定されるデフォルトタイムゾーンが変わってしまいました1。私もこの影響の対応でずいぶん大変な思いをしたものです⋯
マイナーバージョンアップした程度でクエリの意味が9時間変わってしまうのは恐ろしいことですね。タイムゾーンを暗黙でわかってもらうのではなく、明示して変換するように書いてみましょう。それはat time zone構文を使ってこう書きます。
SELECT
some_timestamp at time zone 'Asia/Tokyo' as some_timestamptz,
...
このat time zone構文、timestamp型に対して使うとtimestamp with timezone型を返しますし、逆にtimestamp with timezone型に対して使うとtimestamp型を返すおもしろい定義になっています。
そして、この例ではAsia/Tokyoと都市名指定のタイムゾーンで変換していますが、時間幅指定の+09:00を使いたい場合はちょっと記法に注意が必要です。（Asia/Tokyo表記があるのにわざわざ+09:00と表記するケースがあるものかについては 別記事に書きました）
× some_timestamp at time zone '+09:00'
○ some_timestamp at time zone interval '+09:00'
interval キーワードを置いてinterval型として与える必要があります。これを忘れるとどうなるかはドキュメントに記載が無いですが、実際にやってみると（PG 15.3）⋯ 恐ろしいことに、エラーになるのではなくてなんと9時間足すはずが引いてしまうという正反対の動作をしてくれました⋯！
-
未記述だった場合にはOSのTZ環境変数をとるようになっていたのが、GMT固定に変わった。その代わりに起動時にOSのTZ環境変数値をconfに自動で書き込んでくれる仕様も盛り込まれたのだが、confファイルをansibleなんかで管理しているとこの自動書き込みは上書きされて消えてしまうことに。 ↩