如何在PostgreSQL中获取正确的时区偏移量

你当前的查询逻辑存在误区：

• ('2023-01-01 12:00:00'::timestamptz AT TIME ZONE 'CEST') 会将带时区的时间转换为无时区的timestamp类型，值为CEST时区的2023-01-01 14:00:00（因为UTC+00的12:00对应CEST+02的14:00）。
• 随后强制转换为timestamptz时，PostgreSQL会将这个无时区的时间视为**当前客户端默认时区（UTC）**的时间，因此最终显示为2023-01-01 14:00:00+00。

方法1：设置客户端时区直接显示 #

如果你只是想以CEST时区的偏移格式展示结果，只需临时设置客户端时区为CEST，直接查询原timestamptz即可：

SET timezone = 'CEST';
SELECT '2023-01-01 12:00:00'::timestamptz;

返回结果：2023-01-01 14:00:00+02

方法2：格式化输出指定时区的字符串 #

如果不想修改客户端时区，可以使用to_char函数，指定时区参数来格式化输出带偏移的字符串：

SELECT to_char('2023-01-01 12:00:00'::timestamptz, 'YYYY-MM-DD HH24:MI:SSOF', 'CEST');

返回结果：2023-01-01 14:00:00+02

方法3：构造指定时区的timestamptz值 #

若需要构造一个对应CEST时区14:00的timestamptz值，可以通过无时区时间转换：

SELECT '2023-01-01 14:00:00'::timestamp AT TIME ZONE 'CEST';

该语句会将2023-01-01 14:00:00视为CEST时区的时间，转换为timestamptz存储（内部为UTC的12:00），当客户端时区设为CEST时，查询结果会显示为2023-01-01 14:00:00+02。

注意：PostgreSQL的timestamptz类型本质是以UTC存储时间，显示时的时区偏移由客户端timezone配置决定，以上方法都是围绕调整显示逻辑或正确构造带时区信息的时间来实现需求。

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