PostgreSQL服务器返回UTC时间戳异常问题排查求助

问题原因 #

你遇到的计算错误不是PostgreSQL的bug，是SQL语句中时间类型转换的逻辑错误，核心是混淆了timestamp（无时区时间）和timestamptz（带时区时间）两种类型的语义，以及AT TIME ZONE语法的作用逻辑：

1. now() 函数返回的是timestamptz类型，本质存储的是UTC时间戳，展示时会自动转换为会话设置的时区（你的场景是欧洲/柏林）的时间数值，你执行命令时对应UTC时间为19:47，柏林时间为21:47。
2. 你执行now()::timestamp时，直接丢弃了timestamptz的时区信息，得到的是柏林时区的时间数值：2021-08-18 21:47:15，这个值没有任何时区属性。
3. 对无时区的timestamp使用AT TIME ZONE 'UTC'时，语义是「将这个无时区的时间数值视为UTC时区的时间，转换为带时区的timestamptz类型」，也就是把21:47当成UTC时间，再转换为你会话的柏林时区（+2小时），最终得到23:47，就是你看到的错误结果。

解决方法 #

使用以下任意一种正确写法即可得到正确的UTC时间：

写法1：直接转换时区后格式化 #

不需要额外做类型强制转换，直接对now()返回的timestamptz做时区转换后格式化即可：

SELECT to_char(now() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS');

写法2：先转UTC时区的无时区时间再格式化 #

如果业务需要先拿到UTC时区的无时区时间存储/二次处理，可以先转时区再转timestamp类型：

SELECT to_char((now() AT TIME ZONE 'UTC')::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS');

排查技巧 #

后续遇到时区相关问题时，可以先执行以下两个语句对比，明确两种时间类型的差异：

-- 查看带时区的当前时间（按会话时区展示，本质是UTC时间戳）
SELECT now();
-- 查看无时区的当前时间（仅保留会话时区的时间数值，无时区属性）
SELECT LOCALTIMESTAMP;

内容的提问来源于stack exchange，提问作者Marco Afonso