Postgresの「AT TIME ZONE 'UTC'」は思った通りに動かない
Footguns with Postgres "at time zone 'UTC'"
Postgresの「AT TIME ZONE 'UTC'」は、timestamptzをタイムゾーン情報のないtimestampに変換する。そのため、timestampとtimestamptzの比較は常にfalseになり、月の加算もタイムゾーン依存になる。UTCで運用する場合、月を加算する前に一度UTCに変換し、さらに結果をtimestamptzに戻すためにもう一度「AT TIME ZONE 'UTC'」を適用する必要がある。
timestampとtimestamptzの等価比較は常にfalseになる。
HNでの議論
46- heurekamala
このテキストには「timestampとtimestamptzを比較すると常にfalseになる」と書いてある。
それは間違いだ。Postgresはバックグラウンドで非常に静かにtimestampをtimestamptzに変換する。
その際にセッションのタイムゾーンを使うかどうか。
trueになるかfalseになるかはTimeZone設定次第だ。これは「常にfalse」よりもさらに悪い。
UTCを使う本番環境ではうまく動く。カリフォルニアの開発者のラップトップでは動かない。
実際に試した:
SET TIME ZONE 'America/Los_Angeles';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* f */
SET TIME ZONE 'UTC';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* t */
可能ならPostgres 16を使うといい。
テキストにある二重のAT TIME ZONE 'UTC'はそこでは不要だ。そこでは第三引数にタイムゾーンを取るdate_addがある:
date_add(b.month_start, interval '1 month', 'UTC')
これはUTCで月を加算し、結果はtimestamptzのままになる。
- ulrikrasmussen
SQL標準は残念ながら時間の扱いに関して本当にひどい。型`timestamp`はタイムスタンプでは全くない。なぜなら一意な時点をエンコードしないからだ。単に日付と時刻を保存するだけで、それはタイムゾーンに対して解釈されなければならない。これは「datetime」と呼ばれるべきだ。
JavaのInstantをデータベースとの間で行き来させるのも、正しく行うのは驚くほど難しい作業だ。しかもJDBCのsetTimestamp/getTimestampメソッドを使うと完全に間違った扱いをするので、なおさらだ。これは設計が悪くて落とし穴があるからではなく、実装が単純に間違っていて、カレンダー日付が十分に過去であるInstantを扱うとデータを破壊するからだ。過去の日付に対してグレゴリオ暦に切り替わるレガシーな日付/時刻APIを使っているためだ。
`timestamp with time zone`という名前も誤解を招く。実際にはタイムゾーンを保存せず、java.time.Instantのようにエポックからの秒数を保存する（解像度は異なるが）。「with time zone」の部分は、値を記述するテキスト形式を指すだけで、その形式は日付/時刻部分の後にタイムゾーンを含めてタイムスタンプを一意に識別する。しかし値がパースされた後、タイムゾーンは捨てられ保存されない。これはJavaの`ZonedDateTime`などとは異なる。ZonedDateTimeは実際にオフセットを保存するため、(Instant, TimeZone)のペアに対応する。
- 1a527dd5
- Macha
私の経験では（Postgres開発者の使うべきという助言にもかかわらず）、timestamptzはほとんど役に立たないデータ型だ。なぜならUTCへの変換をラップしただけのものだから。
- 過去のイベントを保存する？ 単にUTCで保存すればいい。timestamptzを使って自動変換させることもできるが、実際にはtimestampよりも不透明なインターフェースだ。
- 未来のUTC時刻を保存する？ 素晴らしい、単にUTCを使えばいい。上記参照。
- 未来の人間の時刻を保存する？ その場合timestamptzは積極的に有害だ。なぜなら熱心にUTCに変換してしまうので、イベントの期日までに更新されたtzdbを入手したとしても、書き込み時に何が起きたか分からず、日時が曖昧になる。プレーンなtimestamp + 文字列のタイムゾーンカラムを使う方がまだマシだ（それでソートする必要があるなら、非正規化した_utcカラムも使うかもしれない。tzdbを更新するときに再生成するか、多少のオフバイワンエラーを受け入れる必要があると理解した上で。しかし少なくとも入力値が何だったか分かっているときにそれができる。timestamptzとは違って）。
- layer8
> + INTERVAL '1 months'で月を加算するのはタイムゾーン依存だ。[…]
月の加算はそもそも、dateを使ったとしても、月の日が28より大きい場合には明確に定義されていない。システムがそのような計算を一般的に許すのは間違いだと思う（アプリケーションコードがドメイン固有のビジネスルールを実装するのとは対照的に）。