PostgreSQL月统计查询1996年8月数据被错误归入7月问题修复

错误原因 #

你当前使用的时区转换逻辑顺序有误：ts :: timestamptz AT TIME ZONE 'UTC' 操作会先将无时区的ts字段按数据库会话的本地时区转换为带时区时间戳，再转成UTC的无时区时间戳。当本地时区早于UTC（例如东八区）时，当月首日零点的时间会被转换为UTC上月的末尾时间，最终导致date_trunc统计月份时出现偏移。

修正方案 #

场景1：按UTC时区统计月度数据（ts字段为UTC时间存储的无时区时间戳） #

SELECT
    ts,
    date_trunc('month', ts AT TIME ZONE 'UTC') AS month,
    activity,
    revenue_impact
FROM activity_stream
WHERE activity = 'completed_order'
ORDER BY month

场景2：按指定本地时区统计月度数据（例如东八区Asia/Shanghai） #

SELECT
    ts,
    date_trunc('month', ts AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai') AS month,
    activity,
    revenue_impact
FROM activity_stream
WHERE activity = 'completed_order'
ORDER BY month

逻辑说明 #

先通过ts AT TIME ZONE 'UTC'声明原始ts字段的时区属性，得到准确的带时区时间戳后，再转换到你需要统计的目标时区，最后执行date_trunc取月份，即可避免跨月归类错误。

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