Postgresの「AT TIME ZONE 'UTC'」は思った通りに動かない

Footguns with Postgres "at time zone 'UTC'"

▲ 166birdculture110 HNでの議論
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での議論RSS

HNでの議論

110
  1. 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のままになる。

  2. tibbar

    この夏、AIの助けを借りて、datetime_ops btreeファミリ[0]におけるDSTの春の切り替えギャップ下でのtimestamp対timestamptz比較の扱い方に不整合を見つけた。基本的に、式B > AかつB < CだがC = Aでもあるというシナリオが存在し、btreeインデックスを使うクエリ（他にもある）が誤った結果を返す可能性がある。

    メーリングリストでの評価は、これはバグだが、修正する良い方法はないというものだった。

    > このような振る舞いの変更をバックパッチするのは恐ろしく危険に思える。

    今のところ、マスターで何を変えられるか考えているだけだ。今のところ、どの選択肢も好きになれない :-(

    [0] https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-...

  3. ulrikrasmussen

    SQL標準は残念ながら時間の扱いに関して本当にひどい。型`timestamp`は全くタイムスタンプではない。なぜなら一意の時点をエンコードせず、単に日付と時刻を保存し、それはタイムゾーンに対して解釈される必要があるからだ。これは「datetime」と呼ばれるべきだ。

    JavaのInstantをデータベースとの間で行き来させるのも、正しく行うのは驚くほど難しい作業であり、JDBCのsetTimestamp/getTimestampメソッドを使うと完全に間違った扱いをするのは助けにならない。設計が悪く落とし穴があるからではなく、実装が単純に間違っており、レガシーな日付/時刻APIを使用しているため、過去の日付のカレンダー日付が十分に古いInstantを扱うとデータが破損するからだ。

    `timestamp with time zone`という名前も誤解を招く。なぜなら実際にはタイムゾーンを保存せず、java.time.Instantのようにエポックからの秒数を保存するからだ（解像度は異なるが）。「with time zone」の部分は、値を記述するテキスト形式を指し、日付/時刻部分の後にタイムゾーンを含めてタイムスタンプを一意に識別するが、値がパースされた後はタイムゾーンは捨てられ保存されない。これはJavaの`ZonedDateTime`などとは異なり、実際にオフセットを保存するため、(Instant, TimeZone)のペアに対応する。

  4. 1a527dd5

    私のバイブル: https://wiki.postgresql.org/wiki/Don't_Do_This

  5. beybol

    私はデータベースレベルでの時間計算に依存していない。データベースに保存されたUTC時間とユーザーのタイムゾーンに基づいてアプリケーションで処理する。これにより問題はアプリケーションコードに移り、そこで正確に制御でき、日が終わる時や異なるタイムゾーンにまたがるイベントの扱いなど、特定のビジネス要件に対して意識的な決定ができる。また、すべてのケースをユニットテストで適切にテストすることも可能になる。

  6. layer8

    > + INTERVAL '1 months'で月を加算するのはタイムゾーン依存だ。[…]

    月の加算は、日付を使っても、月の日が28より大きい場合、そもそも明確に定義されていない。システムがそのような計算を一般的に許可するのは間違いだと思う（アプリケーションコードがドメイン固有のビジネスルールを実装するのとは対照的に）。

  7. Felk

    混乱のほとんどは、「AT TIME ZONE」がタイムゾーン付きタイムスタンプへの変換とからの変換の両方の構文であることから来ていると思う。もし二つの操作に異なる表現があればもっと直感的だったかもしれない。例えば:

    timestampからtimestamptzへ: AS ZONED AT TIME ZONE ...

    timestamptzからtimestampへ: AS LOCAL AT TIME ZONE ...

  8. wodenokoto

    これを理解するのに本当に苦労している。Postgresで働いているわけではないので大目に見てほしい。

    どうやらtimestampとtimestamptzはどちらもUnixエポックに似た方法で日時を表す8ビット整数を保存するらしい。

    どちらもタイムゾーンやオフセットを保存しない。

    では、それらは何なのか？

この日のほかの記事

2026-09-28