在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|

我要做的是将时间转换为UTC格式。当我尝试这样做时，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)

我添加了一个内置的date_bin()函数，以提高可读性和易用性。它做的是完全一样的事情，而且它让你调整你的时间"桶"。任意，不只是四舍五入/截断到整个单位

:

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'