我撰寫了一個查詢，將指標分組到 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？
uj5u.com熱心網友回復：
您可以將 移動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'
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/535286.html