Confused converting timestamp to local time in PostgreSQL-CodePudding

I've written a query that groups metrics into 5 minute buckets and counts the number of occurences in each bucket.

This is the query:

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;

The results look like so:

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

What I want to do is convert the time so that it's in UTC. When I try to do this, 11 hours is added to time, presumably because PostgreSQL thinks the time is already in 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;

The data now looks like this:

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

I want it to be:

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

How can I make PostgreSQL treat the time column as 'Australia/Sydney' time and then convert this to UTC?

CodePudding user response：

You can move the at time zone 'Australia/Sydney' to where you interpret _received and make sure your timestamp in _received isn't already aware of the time zone (being of type 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)

I added a built-in date_bin() function for improved readability and ease of use. It does exactly the same thing, plus it lets you align your time "buckets" arbitrarily, not just round/truncate to whole units.

Before:

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)

What-could-go-wrong cheatsheet for text '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'