Confused converting timestamp to local time in PostgreSQL

Question

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?

How about 'epoch' from _received at time zone 'Australia/Sydney' — Zegarek
– Zegarek, Commented Nov 16, 2022 at 11:30
@LaurenzAlbe Good point. If _received happens to be a timestamptz, my initial answer needs a ::timestamp to cheat it. — Zegarek
– Zegarek, Commented Nov 16, 2022 at 12:25
Fixed it, plus added a date_bin() that OP seems to be emulating through the extract+divide+floor+multiply+cast operation. — Zegarek
– Zegarek, Commented Nov 16, 2022 at 12:52
I think it would be good to clarify the rationale behind this whole operation: it's not uncommon that people want to "convert" between time zones without knowing all time zone aware date/times are stored the same, uniform timezone implied, and "timezone layer" is there just to handle offsets in read/write interpretation. There could be a scenario where some data was fed with the wrong time zone info, so it has to be found and corrected, but this doesn't seem to be the case here. — Zegarek
– Zegarek, Commented Nov 16, 2022 at 12:54

Zegarek · Accepted Answer · 2022-11-16 15:56:49Z

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'

Collectives™ on Stack Overflow

Confused converting timestamp to local time in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related