Postgres时区偏移不符合预期问题排查

问题出在时区转换的类型逻辑上：

• 执行timestamptz AT TIME ZONE 'tz'时，返回的是无时区的timestamp类型，PostgreSQL不会记录该时间所属的时区信息。
• 后续对这个无时区timestamp执行AT TIME ZONE 'UTC'时，PostgreSQL会默认将它视为UTC时区的时间处理，自然无法转回原本的UTC时间。

要实现正确的来回转换，必须明确告诉PostgreSQL：第二次得到的无时区时间属于America/New_York时区，再基于带时区的时间转回UTC。

修正后的SQL语句 #

SELECT 
  '2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC',
  '2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York',
  -- 先将纽约时区的无时区时间标记为America/New_York时区，再转回UTC
  ('2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') 
  AT TIME ZONE 'America/New_York' 
  AT TIME ZONE 'UTC'

执行后将得到预期结果：

2023-04-03 04:00:00.000000
2023-04-03 08:00:00.000000
2023-04-03 04:00:00.000000

更简洁的写法（推荐） #

一开始就使用timestamptz（带时区的时间戳）类型，能更清晰地处理时区转换：

SELECT 
  ts_utc,
  ts_utc AT TIME ZONE 'America/New_York',
  (ts_utc AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC'
FROM (SELECT '2023-04-03T04:00:00.000Z'::timestamptz AS ts_utc) t

本质逻辑：无时区timestamp必须先关联到对应时区，转成timestamptz后，才能正确转换到其他时区。

内容的提问来源于stack exchange，提问作者Jason Fill