Re: BUG #17240: <timestamptz> at time zone ... ; wrong result

PG Bug reporting form <noreply(at)postgresql(dot)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