From: Tom Lane <tgl@sss.pgh.pa.us>
To: marek.lall@eesti.ee
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17240: <timestamptz> at time zone ... ; wrong result
Date: Thu, 21 Oct 2021 09:41:00 -0400
Message-ID: <2402843.1634823660@sss.pgh.pa.us> (raw)
In-Reply-To: <17240-cdccc24b9eee2d2c@postgresql.org>
References: <17240-cdccc24b9eee2d2c@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> # select now()
> , now() at time zone 'America/Los_Angeles' as correct
> , now() at time zone '-07:00:00' as wrong;
Unfortunately, the pure-numeric syntax for time zone names follows the
POSIX sign convention, which is opposite to the ISO convention used
in pg_timezone_names.utc_offset (and in most other places in Postgres).
So "at time zone '+07:00:00'" is what you needed to write to duplicate
the 'America/Los_Angeles' result. See
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
Now, if you'd done this:
select ... now() at time zone interval '-07:00:00' as fine
you'd have gotten the ISO sign interpretation. But an undecorated
literal string defaults to being of type text, meaning you get
the time-zone-name logic path.
The great thing about standards is there are so many to choose from :-(
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, marek.lall@eesti.ee, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17240: <timestamptz> at time zone ... ; wrong result
In-Reply-To: <2402843.1634823660@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox