PostgreSQL带时区时间戳跨时区转换并保留时区的实现

我的PostgreSQL数据库中，timestamptz类型字段存储的是UTC时区的时间。我希望查询时将其转换为澳大利亚悉尼时区（+10），同时保留时区信息。

尝试过的SQL语句：

SELECT 
    '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC',
    timezone('Australia/Sydney', '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC')
;

返回的第二个字段是无时区的时间戳："2022-05-05 00:00:01.546"，期望得到带时区的结果："2022-05-05 00:00:01.546+10"（timestamptz类型）。

还尝试了：

select
    ts,
    timezone('Australia/Sydney', ts),
    ts at time zone 'Australia/Sydney'
from timeseries
limit 1

仍未得到预期结果。需要将结果映射到pyarrow.Table，希望自动推断出正确类型timestamp[ns, tz=Australia/Sydney]，而非timestamp[ns, tz=UTC]。

解决方案 #

要得到带悉尼时区信息的timestamptz结果，核心是先将原UTC时区的timestamptz转换为悉尼时区的无时区时间戳，再重新标记为悉尼时区的带时区类型，以下两种方法均可实现：

方法1：使用AT TIME ZONE组合转换 #

SELECT 
    ts,
    (ts AT TIME ZONE 'Australia/Sydney')::timestamptz AT TIME ZONE 'Australia/Sydney' AS ts_sydney
FROM timeseries
LIMIT 1;

• 第一步ts AT TIME ZONE 'Australia/Sydney'：把UTC的timestamptz转换为悉尼时区的无时区timestamp类型
• 第二步::timestamptz AT TIME ZONE 'Australia/Sydney'：将无时区时间戳重新转换为带悉尼时区信息的timestamptz类型

方法2：嵌套使用timezone函数 #

SELECT 
    ts,
    timezone('Australia/Sydney', timezone('Australia/Sydney', ts)) AS ts_sydney
FROM timeseries
LIMIT 1;

• 内层timezone('Australia/Sydney', ts)：将UTC的timestamptz转换为悉尼时区的无时区时间戳
• 外层timezone('Australia/Sydney', ...)：将无时区时间戳转换为带悉尼时区的timestamptz类型

效果验证 #

执行上述任意查询后，返回的ts_sydney字段会带有悉尼时区的偏移信息（非夏令时为+10，夏令时为+11），且类型为timestamptz。当映射到pyarrow.Table时，会自动推断为timestamp[ns, tz=Australia/Sydney]类型，符合预期需求。

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