PostgreSQL 如何输出带澳大利亚/悉尼时区偏移的JSON格式时间戳

问题根因 #

• 数据库默认会话时区为UTC时，timestamptz 类型调用to_json()、to_char()函数序列化输出时，会默认携带UTC的+00:00偏移，不会自动匹配目标时区的冬/夏令时规则，也就是你测试时得到的"2021-01-31T07:47:22.895185+00:00"、2021-01-31T07:47:22:895+00结果的来源。
• 之前尝试加AT TIME ZONE 'AEDT'写法失效，核心是两个逻辑误区：
  1. AEDT只是澳大利亚东部夏令时的缩写，没有绑定悉尼时区完整的冬/夏令时自动切换规则，仅能对应固定的+11偏移，无法覆盖冬令时+10的场景
  2. AT TIME ZONE对timestamptz的转换会输出不带时区标识的timestamp without time zone类型，这类类型本身不携带时区属性，后续输出永远会被固定标记为+00:00，还会把原时间值强制偏移到目标时区的本地数值，出现时间错位

正确实现方案 #

方案1：会话级时区调整配合to_json直接输出（推荐） #

PostgreSQL的to_json()函数处理timestamptz类型时，会自动按照当前会话配置的时区，计算对应时间值和匹配的偏移量，完全匹配要求的输出格式：

-- 全局会话设置时区写法
SET timezone = 'Australia/Sydney';
-- 直接序列化输出即可
SELECT to_json(current_timestamp::timestamptz);

-- 单查询临时设置时区写法，不影响其他会话配置
SET LOCAL timezone = 'Australia/Sydney';
SELECT to_json(current_timestamp::timestamptz);

*效果说明：

• 南半球冬令时时段（每年4月第一个周日到10月第一个周日），自动输出+10:00偏移，对应示例："2021-07-31T23:59:59.123456+10:00"
• 南半球夏令时时段（每年10月第一个周日到次年4月第一个周日），自动输出+11:00偏移，对应示例："2021-01-31T23:59:59.123456+11:00"

方案2：自定义to_char格式输出 #

如果需要用to_char自定义拼接格式，不要在原UTC时区的timestamptz上直接套格式符，要先将时间值转换为悉尼时区的本地数值，再动态拼接匹配的偏移量：

SELECT
  -- 拼接悉尼时区下的本地时间值（6位微秒精度）
  to_char(
    current_timestamp AT TIME ZONE 'Australia/Sydney',
    'YYYY-MM-DD"T"HH24:MI:SS.US'
  )
  -- 动态拼接当前时间对应的时区偏移，不用手动写死
  || to_char(current_timestamp, 'OF');

*格式符说明：

• 原来的格式串把秒和毫秒用冒号分隔，导致输出为2021-01-31T07:47:22:895+00的错位格式；改用US格式符可输出6位微秒精度，用点做分隔符对齐示例格式
• OF格式符会自动计算当前timestamptz对应的时区偏移值（比如+10:00、+11:00），避免硬编码带来的冬夏令时适配问题

避坑提示 #

• 不要用简短的时区缩写（比如AEDT、AEST）作为时区配置值，这类缩写不关联完整的时区规则表，无法自动完成冬夏令时切换
• 不要将AT TIME ZONE转换得到的无时区timestamp类型直接序列化，这类类型输出永远会携带+00:00偏移，和目标时区不匹配

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