在PostgreSQL中将时间戳转换为本地时间的混淆

Question

我编写了一个查询，将度量指标分组到5分钟的桶中，并计算每个桶中发生的次数。

这是一个查询：

select count(*)                                                           as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end    as metric,
       to_timestamp(floor((extract('epoch' from _received) / 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;

结果如下：

| amnt | metric | time |
| -------- | -------------- | -------------- |
| 1584| indicative| 2022-11-16 21:25:00.000000 +00:00|
| 36290 | executable| 2022-11-16 21:25:00.000000 +00:00|
| 1250| indicative| 2022-11-16 21:25:00.000000 +00:00|
| 53074| executable| 2022-11-16 21:25:00.000000 +00:00|

我想要做的是转换时间，以便它在世界协调时。当我尝试这样做的时候，11个小时被加到时间里，大概是因为PostgreSQL认为时间已经在UTC了。

select count(*)                                                           as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end    as metric,
       to_timestamp(floor((extract('epoch' from _received) / 300)) * 300) at time zone 'Australia/Sydney' at time zone 'UTC' 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;

现在的数据如下：

| amnt | metric | time |
| -------- | -------------- | -------------- |
| 1584| indicative| 2022-11-17 08:25:00.000000 +00:00|
| 36290 | executable| 2022-11-17 08:25:00.000000 +00:00|
| 1250| indicative| 2022-11-17 08:30:00.000000 +00:00|
| 53074| executable| 2022-11-17 08:30:00.000000 +00:00|

我希望它是：

| amnt | metric | time |
| -------- | -------------- | -------------- |
| 1584| indicative| 2022-11-16 10:25:00.000000 +00:00|
| 36290 | executable| 2022-11-16 10:25:00.000000 +00:00|
| 1250| indicative| 2022-11-16 10:30:00.000000 +00:00|
| 53074| executable| 2022-11-16 10:30:00.000000 +00:00|

如何使PostgreSQL将时间列视为“澳大利亚/悉尼”时间，然后将其转换为UTC？

Answer 1

您可以将at time zone 'Australia/Sydney'移到解释_received的位置，并确保_received中的时间戳不知道时区(属于timestamptz/timestamp with time zone类型)：

select count(*)                                                        as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end as metric,
       date_bin('5 minutes',_received::timestamp,'today') at time zone 'Australia/Sydney' 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;
-- amnt |   metric   |          time
--------+------------+------------------------
--    1 | indicative | 2022-11-16 10:25:00+00
--    1 | executable | 2022-11-16 10:25:00+00
--    1 | indicative | 2022-11-16 10:30:00+00
--    1 | executable | 2022-11-16 10:30:00+00
--(4 rows)

为了提高可读性和易用性，我增加了一个内置的宾()函数。它做的是完全相同的事情，而且它可以让你的时间“桶”任意地对齐，而不仅仅是圆整/截断到整个单位。

在此之前：

table feedintra;--raw test data
-- firmness |      _received
------------+---------------------
--       89 | 2022-11-16 21:25:00
--       90 | 2022-11-16 21:25:00
--        0 | 2022-11-16 21:30:00
--       90 | 2022-11-16 21:30:00
--(4 rows)

select count(*)                                                           as amnt,
       case when firmness < 90 then 'indicative' else 'executable' end    as metric,
       to_timestamp(floor((extract('epoch' from _received) / 300)) * 300) at time zone 'Australia/Sydney' at time zone 'UTC' 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;
-- amnt |   metric   |        time
--------+------------+---------------------
--    1 | indicative | 2022-11-17 08:25:00
--    1 | executable | 2022-11-17 08:25:00
--    1 | indicative | 2022-11-17 08:30:00
--    1 | executable | 2022-11-17 08:30:00
--(4 rows)

文本'2022-11-16 21:25:00.000000 +00:00'::text的什么-可能出错？

         output         |                               interpretation
------------------------+-----------------------------------------------------------------------------
 2022-11-16 21:25:00+00 | ::timestamp
 2022-11-16 10:25:00+00 | ::timestamp at time zone 'Australia/Sydney'
 2022-11-16 10:25:00+00 | ::timestamp at time zone 'Australia/Sydney' at time zone 'UTC'
 2022-11-16 21:25:00+00 | ::timestamp at time zone 'UTC'
 2022-11-16 21:25:00+00 | ::timestamptz
 2022-11-17 08:25:00+00 | ::timestamptz at time zone 'Australia/Sydney'
 2022-11-17 08:25:00+00 | ::timestamptz at time zone 'Australia/Sydney' at time zone 'UTC'
 2022-11-16 21:25:00+00 | ::timestamptz at time zone 'UTC'
 2022-11-16 21:25:00+00 | ::timestamptz::timestamp
 2022-11-16 10:25:00+00 | ::timestamptz::timestamp at time zone 'Australia/Sydney'
 2022-11-16 10:25:00+00 | ::timestamptz::timestamp at time zone 'Australia/Sydney' at time zone 'UTC'
 2022-11-16 21:25:00+00 | ::timestamptz::timestamp at time zone 'UTC'