PostgreSQL中基于单独时区列转换UTC时间的方法

当然可以！PostgreSQL原生就支持针对每行的时区值动态转换UTC时间戳，你的思路完全正确，这里给你梳理更准确的实现方式和细节说明：

核心实现代码 #

首先修正你代码里的小笔误（你用了interval_timestamp_utc，但表结构里对应的列是datetime_utc），优化后的查询语句如下：

SELECT
    -- 转换为指定时区的日期
    (datetime_utc AT TIME ZONE time_zone)::DATE AS "Reporting Date",
    -- 转换为指定时区的时间间隔（HH24:MI格式）
    TO_CHAR(datetime_utc AT TIME ZONE time_zone, 'HH24:MI') AS "Reporting Interval",
    -- 可选：返回完整的转换后时间戳，方便验证结果
    datetime_utc AT TIME ZONE time_zone AS "Reporting Timestamp"
FROM your_table_name;

代码说明 #

1. 时区转换逻辑：
   • 你的datetime_utc是timestamptz（带时区的时间戳）类型，PostgreSQL会自动识别其UTC基准。使用AT TIME ZONE time_zone语法时，PostgreSQL会将这个UTC时间戳转换为time_zone列指定时区的无时区时间戳（timestamp without time zone）——因为此时时间已经绑定到目标时区，无需再携带时区信息。
2. 日期和时间提取：
   • 直接将转换后的时间戳转成DATE类型，就能得到目标时区下的自然日期。
   • 用TO_CHAR()函数格式化时间部分，得到你需要的HH24:MI格式时间间隔。

性能说明 #

这种逐行动态转换是高效的：PostgreSQL对时区转换的处理经过高度优化，只要datetime_utc和time_zone列没有大量无效值（比如非法时区字符串），查询速度不会有明显瓶颈。如果你的数据量极大，且时区值重复率很高，可以考虑预计算转换结果并存储，但绝大多数场景下动态转换足够满足需求。

示例验证 #

针对你提供的示例数据：

date       interval datetime_utc          time_zone
---------- -------- --------------------- ----------------
2018-05-11 22:30    2018-05-10 03:30:00   America/New_York

执行查询后会得到：

Reporting Date | Reporting Interval | Reporting Timestamp
---------------|--------------------|----------------------
2018-05-09     | 23:30              | 2018-05-09 23:30:00

（注：2018年5月纽约处于夏令时，UTC偏移为-4小时，03:30 UTC减去4小时即为前一天的23:30）

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