PostgreSQL中timestamp时区转换与其他日期类型转换行为相反

这不是PostgreSQL的异常行为，而是AT TIME ZONE操作符的双向转换逻辑导致的——它会根据输入数据类型的不同，执行完全相反的时区转换方向：

两种核心转换逻辑 #

• 当输入是带时区时间戳（timestamptz）、date或可解析为timestamptz的字符串时：
  输入 AT TIME ZONE '时区' → 将输入的UTC时间转换为目标时区的本地时间，返回不带时区的timestamp类型。
• 当输入是不带时区时间戳（timestamp）时：
  输入 AT TIME ZONE '时区' → 将输入的时间视为目标时区的本地时间，转换为对应的UTC时间，返回带时区的timestamptz类型。

逐个分析你的查询结果 #

假设你的会话时区为UTC（默认配置通常如此），拆解每个字段的转换过程：

1. str_only: '2025-02-20' AT TIME ZONE 'Asia/Kolkata' #

PostgreSQL自动将字符串解析为UTC时区的timestamptz（即2025-02-20 00:00:00+00），然后转换为Asia/Kolkata时区的本地时间：UTC的00:00加上5小时30分的时差，得到2025-02-20 05:30:00（不带时区的timestamp）。

2. cast_to_date: '2025-02-20'::date AT TIME ZONE 'Asia/Kolkata' #

date类型会先被转换为UTC时区的timestamptz（当天午夜2025-02-20 00:00:00+00），后续转换逻辑和str_only完全一致，最终得到2025-02-20 05:30:00。

3. cast_to_tz: '2025-02-20'::timestamptz AT TIME ZONE 'Asia/Kolkata' #

显式声明的timestamptz默认是UTC时区的2025-02-20 00:00:00+00，转换为Asia/Kolkata时区的本地时间，结果同样是2025-02-20 05:30:00。

4. cast_to_ts: '2025-02-20'::timestamp AT TIME ZONE 'Asia/Kolkata' #

这里输入的是不带时区的timestamp（2025-02-20 00:00:00），PostgreSQL会把这个时间看作是Asia/Kolkata时区的本地时间，然后转换为对应的UTC时间：Asia/Kolkata的00:00减去5小时30分的时差，得到UTC时间2025-02-19 18:30:00+00（带时区的timestamptz）。

总结 #

你看到的“相反结果”，本质是转换方向的差异：前三个字段是UTC→目标时区的转换，而timestamp输入的字段是目标时区→UTC的转换，这完全符合AT TIME ZONE的设计逻辑。

内容的提问来源于stack exchange，提问作者Hemanth S. Vaddi