Convert postgres timestamp with timezone between timezones

Question

I have data stored in a timestamp with timezone column (timestamptz) in UTC.

I want to query in a different timezone (Australia/Sydney +10) but retain the timezone

SELECT 
    '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC',
    timezone('Australia/Sydney', '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC')
;

This returns:

"2022-05-04 14:00:01.546+00" timestamp with time zone

"2022-05-05 00:00:01.546" timestamp without time zone

What I want is:

"2022-05-05 00:00:01.546+10" timestamp with time zone

i.e. convert from +00 to +10 and retain the time zone. I keep finding examples that convert to local time without the timezone info. I'm trying to map to a pyarrow.Table and want to infer the correct type timestamp[ns, tz=Australia/Sydney] rather than timestamp[ns, tz=UTC]

Edit: To make it clearer I've tried the following:

select
    ts,
    timezone('Australia/Sydney', ts),
    ts at time zone 'Australia/Sydney'
from timeseries
limit 1

I think all you need is the_column at time zone 'Australia/Sydney' — user330315
– user330315, Commented Mar 7, 2023 at 7:14
That gives me the same as timezone('Australia/Sydney', the_column) - a timestamp without timezone of value "2022-05-05 00:00:01.546". At least when I run it in pgAdmin — David Waterworth
– David Waterworth, Commented Mar 7, 2023 at 8:15
That will most certainly return a timestamp WITH time zone — user330315
– user330315, Commented Mar 7, 2023 at 8:20
That what I was expecting - but I've pasted in the output from pdAdmin and its a localised naive timezone from what I can see. — David Waterworth
– David Waterworth, Commented Mar 7, 2023 at 8:22

Laurenz Albe · Accepted Answer · 2023-03-07 08:19:45Z

1

The way PostgreSQL works, the only way to get the output 2022-05-05 00:00:01.546+10 is to set the timezone parameter to a time zone with that UTC offset:

SET timezone = 'Australia/Sydney';

answered Mar 7, 2023 at 8:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Waterworth Over a year ago

Ah right, yes that works. I'm not sure I can send multiple statements using connectorx but thanks anyway. I think I might use UTC and convert the pyarrow.Table column on the client.

Collectives™ on Stack Overflow

Convert postgres timestamp with timezone between timezones

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related