PostgreSQL中timestamptz多次时区转换结果异常问题咨询

首先看你执行的SQL查询：

select
  s.start_time::timestamptz at time zone 'America/New_York' as t1,
  s.start_time::timestamptz at time zone 'UTC' as t2,
  s.start_time::timestamptz at time zone 'UTC' at time zone 'America/New_York' as t3,
  s.start_time
from
  my_table s
order by
  id desc;

核心原因：两次AT TIME ZONE的类型转换逻辑是双向的 #

PostgreSQL里AT TIME ZONE的行为完全取决于操作数的类型：

• 当操作数是带时区的timestamptz时，AT TIME ZONE '时区'会返回不带时区的timestamp，代表该时区下的本地时间。比如t1就是把带时区的时间戳转成纽约时区的本地时间（timestamp类型）。
• 当操作数是不带时区的timestamp时，AT TIME ZONE '时区'会把这个时间当作该时区的本地时间，转换为timestamptz（本质是转成对应的UTC时间存储）。

再拆解t3的执行逻辑：

1. 第一步：s.start_time::timestamptz at time zone 'UTC' → 得到UTC时区的本地时间（timestamp类型，不带时区）
2. 第二步：再执行at time zone 'America/New_York' → 把刚才的UTC本地时间错误地当作纽约时区的本地时间，转成timestamptz（最终对应UTC时间会比原时间差8-9小时，取决于夏令时），这和你预期的“把UTC时间转成纽约时间”完全相反。

举个实际例子：假设start_time对应的UTC时间是2024-05-20 12:00:00

• t1：转成纽约时区（夏令时UTC-4），得到2024-05-20 08:00:00（timestamp类型）
• t3第一步：得到UTC本地时间2024-05-20 12:00:00（timestamp）；第二步：把这个时间当作纽约时间转成timestamptz，对应UTC时间是2024-05-20 16:00:00，最终显示的本地时间（你的时区+1）是2024-05-20 17:00:00，自然和t1完全不同。

让t3和t1相等的正确写法 #

如果想通过UTC中转得到和t1一致的结果，需要明确把第二步的结果转成纽约时区的本地时间：

select
  s.start_time::timestamptz at time zone 'America/New_York' as t1,
  s.start_time::timestamptz at time zone 'UTC' as t2,
  -- 正确写法：将UTC的timestamp转成纽约时区的本地时间
  (s.start_time::timestamptz at time zone 'UTC')::timestamp at time zone 'America/New_York' as t3,
  s.start_time
from
  my_table s
order by
  id desc;

当然更直接的方式就是直接复用t1的逻辑，没必要多一次UTC中转。

内容的提问来源于stack exchange，提问作者Karol Selak