GMT数据用AT TIME ZONE转纽约时区仍返回UTC时间戳如何解决

问题底层逻辑 #

你碰到的是AT TIME ZONE语法的双语义特性，PostgreSQL、Redshift、BigQuery等大部分支持该语法的数据库都遵循这个规则：

• 当左侧是*带时区的时间戳（timestamptz）*时，AT TIME ZONE '目标时区'返回的是不带时区标记的timestamp类型，值就是目标时区的当地时间——这就是你拿到的9:30是正确值的原因。
• 当左侧是*不带时区的时间戳（timestamp）*时，AT TIME ZONE '目标时区'返回的是带时区的时间戳（timestamptz），会把左侧的时间值当成目标时区的当地时间，转换为标准的带时区时间戳存储。

你之前的写法得到的是不带时区的9:30，后续直接输出或转timestamptz时，数据库默认用UTC给它加了时区标记，就出现了时间对但时区标错的问题。

正确调整方案 #

按你的需求分两种场景处理：

场景1：需要得到带纽约时区属性的timestamptz类型值（用于后续时间计算）

套两层AT TIME ZONE即可：

(d.start_time AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York'

逻辑是：

• 第一层AT TIME ZONE先把你存的GMT时间转成纽约当地的9:30（不带时区标记的timestamp）
• 第二层AT TIME ZONE告诉数据库「这个9:30是纽约时区的时间」，转换为带时区属性的timestamptz

场景2：需要直接输出带纽约时区偏移的字符串（比如2021-10-25T09:30:00-04:00）

不需要转类型，直接用数据库的时间格式化函数指定输出时区即可，以PostgreSQL为例：

TO_CHAR(d.start_time AT TIME ZONE 'America/New_York', 'YYYY-MM-DD"T"HH24:MI:SS.USOF')

如果是MySQL的话用DATE_FORMAT结合CONVERT_TZ即可，夏令时偏移会自动生效，不需要手动写死-04或者-05。

避坑提示 #

不要强行用::timestamptz转第一层得到的纽约当地时间，除非你先把会话时区改成America/New_York，否则数据库会默认用UTC或者系统默认时区给时间加标记，反而会出问题。

内容的提问来源于stack exchange，提问作者T.Poe