Horton

Re: Inconsistency of timezones in postgresql

David G. Johnston <david.g.johnston@gmail.com>

View thread
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Chris BSomething <xpusostomos@gmail.com>
Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Date: 2024-08-02T04:13:25Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: add example of sign mismatch with POSIX/ISO-8601 time zones

Please reply-all to keep the list involved.

On Thursday, August 1, 2024, Chris BSomething <xpusostomos@gmail.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.