PostgreSQL中时间戳转UTC时区异常问题求助

问题出在你对_received字段的时区处理逻辑上：如果_received是不带时区的timestamp类型（存储的是澳大利亚悉尼本地时间），直接用extract('epoch' from _received)会让PostgreSQL将其当作UTC时间计算秒数，导致后续时间转换完全反向。

要实现「将悉尼时间按5分钟分桶，再转换为UTC时间」的需求，正确步骤如下：

1. 先将_received标记为悉尼时区的带时区时间（timestamptz）
2. 基于这个带时区时间计算5分钟时间桶
3. 最终得到的就是UTC时区的分桶时间

修正后的查询语句 #

select count(*) as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end as metric,
       -- 先将_received转为悉尼时区的带时区时间，再计算5分钟桶
       to_timestamp(floor((extract('epoch' from (_received AT TIME ZONE 'Australia/Sydney')) / 300)) * 300) as time
from feedintra
where _received >= now()::date
  and firmness is not null
  and firmness between 0 and 90
group by firmness, time
order by time;

逻辑说明 #

• _received AT TIME ZONE 'Australia/Sydney'：把不带时区的_received（悉尼本地时间）转换为带时区的timestamptz，PostgreSQL会自动将其转换为UTC基准的时间表示。
• 后续的extract('epoch' ...)和to_timestamp计算的是UTC时间的5分钟桶，最终结果就是你期望的UTC时区分桶时间。

更简洁的date_trunc写法 #

如果想用更直观的分桶方式，也可以用date_trunc实现：

select count(*) as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end as metric,
       date_trunc('hour', _received AT TIME ZONE 'Australia/Sydney') 
           + interval '5 minutes' * floor(date_part('minute', _received AT TIME ZONE 'Australia/Sydney') / 5) as time
from feedintra
where _received >= now()::date
  and firmness is not null
  and firmness between 0 and 90
group by firmness, time
order by time;

这两种写法都会得到你期望的结果：悉尼时间2022-11-16 21:25对应的UTC时间桶为2022-11-16 10:25:00+00。

内容的提问来源于stack exchange，提问作者David O'Loughlin