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 永远无法相等。想要修复这个隐蔽的 bug，你可能需要重复使用两次 AT TIME ZONE 'UTC' 来确保数据类型正确。这篇文章揭示了 Postgres 时间处理中那些容易被忽视的陷阱，帮你避开数据错误的雷区。

Postgres AT TIME ZONE 'UTC' 并不做你以为它做的事。
阅读原文 →HN 评论区RSS

HN 评论区

110
  1. ekjhgkejhgk

    > 使用 + INTERVAL '1 months' 增加一个月是依赖于时区的。如果你的产品运行在 UTC 下，你必须在用 <timestamptz_column> AT TIME ZONE 'UTC' + INTERVAL '1 months' 增加一个月之前确保时区是 UTC……但现在的结果是一个 timestamp，而不是 timestamptz。

    换个角度想。也许错误在于你竟然相信可以对任何东西加上“1 个月”，并指望你的直觉能派上用场。

  2. heurekamala

    原文说："比较 timestamp 和 timestamptz 永远会得到 false。"

    这是错的。Postgres 会在后台悄无声息地将 timestamp 转换为 timestamptz。

    它是否使用会话的时区来进行此操作。

    结果是真还是假取决于 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。

  3. ulrikrasmussen

    SQL 标准在处理时间方面不幸地非常糟糕。`timestamp` 类型根本不是时间戳，因为它不编码唯一的时间点，它只是存储一个日期和一个时间，必须相对于某个时区来解释。它应该被称为 "datetime"。

    将 Java Instant 在数据库之间来回移动也是一项令人惊讶地难以正确完成的任务，而且 JDBC 在使用其 setTimestamp/getTimestamp 方法时完全处理错误也无济于事。这并不是因为设计本身有陷阱（footguns），而是因为实现本身就是错的，如果你处理的是日历日期足够久远的瞬间，它会损坏你的数据，因为它使用了遗留的日期/时间 API，该 API 对过去的日期切换到了公历。

    `timestamp with time zone` 这个名字也具有误导性，因为它实际上并不存储时区，它像 java.time.Instant 一样存储自纪元以来的秒数（尽管分辨率不同）。“with time zone”部分仅指你在文本格式中表示值的方式，即在日期/时间部分之后包含时区以唯一标识时间戳，但在值被解析后，时区就被丢弃且不再存储。这与 Java 中的 `ZonedDateTime` 不同，后者实际上会存储偏移量，因此对应于一对 (Instant, TimeZone)。

  4. tibbar

    今年夏天，在 AI 的帮助下，我发现了 Postgres 在处理 timestamp 与 timestamptz 比较时存在不一致性，具体是在 DST 春季向前调整（spring-forward gap）期间针对 datetime_ops btree 族 [0] 的情况。本质上存在一些场景，其中表达式 B > A 且 B < C，但同时 C = A，这会导致使用 btree 索引（以及其他东西）的查询返回错误结果。

    邮件列表中的评估认为这是一个 bug，但没有好的修复方法。

    > 回补（backpatching）此类行为变更似乎非常可怕。

    目前我只是在考虑我们能在 master 分支中潜在地改变什么。到目前为止，我不喜欢任何选择 :-)

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

  5. 1a527dd5

    我的圣经：https://wiki.postgresql.org/wiki/Don't_Do_This

  6. beybol

    我不依赖数据库层面的时间计算。我在应用程序中基于存储在数据库中的 UTC 时间和用户的时区来处理它们。这将问题转移到了应用程序代码中，在那里我可以精确控制它，并有意识地决定如何处理特定的业务需求，例如一天何时结束，或者如何处理跨越不同时区的事件。这也使得能够用单元测试正确测试所有情况成为可能。

  7. layer8

    > 使用 + INTERVAL '1 months' 增加一个月是依赖于时区的。[…]

    即使使用 date 类型，增加月份本身定义也不明确，尤其是对于大于 28 的日期。我认为系统通用地允许此类计算（而不是由应用程序代码实现特定领域的业务规则）是一个错误。

  8. Felk

    我认为大部分混淆都源于 "AT TIME ZONE" 既是转换 _from_（从）时区时间戳的语法，也是转换 _to_（到）时区时间戳的语法。也许如果这两个操作有不同的措辞会更直观，例如：

    timestamp 转 timestamptz: AS ZONED AT TIME ZONE ...

    timestamptz 转 timestamp: AS LOCAL AT TIME ZONE ...

同日更多故事

2026-09-28