Postgres exhibits some strange behaviour when parsing time zone nameszones, or I just don't understand how it works.
From the documentation:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
The example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
This seems to imply that 'MST' is interchangeable with 'UTC-7' but the behaviour is opposite to what you'd expect.
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'UTC-7';
Result: 2001-02-17 08:38:40
Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.
Using ISO 8601 time zone notation also yields the opposite from the expected result:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7';
Result: 2001-02-17 08:38:40
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07';
Result: 2001-02-17 08:38:40
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07:00';
Result: 2001-02-17 08:38:40
The only ISO 8601 notation that threw an error was the -0700 notation.
Other gibberish notations are accepted, even though they don't make any sense at all:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7MST';
Result: 2001-02-17 08:38:40
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST-7';
Result: 2001-02-17 08:38:40
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7+7';
Result: 2001-02-17 08:38:40
Can someone help me understand how time zones, specifically offset notation works, or is supposed to work, in Postgres.