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
