Re: Inconsistency of timezones in postgresql

Please reply-all to keep the list involved.

On Thursday, August 1, 2024, Chris BSomething <xpusostomos(at)gmail(dot)com> wrote:

>
> ibis=# set time zone 'UTC';
>>>
>> SET
>
>
From this point on all timestamptz outputs will be shown to you in “+00”
since your time zone is set to UTC

>
>
> ibis=# select update_date at time zone 'Australia/Sydney' from
> collection_item where item_id=2197;
> timezone
> ------------------------
> 2012-07-06 02:59:55+00
>

The input timestamp is noon. Turn it into a string. Concatenate
“Australia/Sydney” to it. Cast that to timestamptz. Then rotate that 12pm
Sydney time to UTC - resulting in 2AM. Print 2am to the screen with a
“+00” suffix to indicate that what you are seeing is a timestamptz value
displayed in your UTC specified time zone.

I.e. your just wrote something similar to (in common terms):

Select ‘2012-07-06 12:59:55+10’::timezone at time zone UTC

David J.