Strange UTC offset time zone parsing in Postgres Postgres exhibits some strange behaviour when parsing time zones, 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.
Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.

It depends whether +/- means east or west of Greenwich, and it turns out both conventions exist. PostgreSQL doc warns about that:

http://www.postgresql.org/docs/current/static/datatype-datetime.html

Excerpt from 8.5.3. Time Zones (but you really want to read the whole paragraph):


  Another issue to keep in mind is that in POSIX time zone names,
  positive offsets are used for locations west of Greenwich. Everywhere
  else, PostgreSQL follows the ISO-8601 convention that positive
  timezone offsets are east of Greenwich.


Using ISO 8601 time zone notation also yields the opposite from the expected result:

In the same page, the accepted formats for time zone literals are listed, here's a condensed version:


  PostgreSQL allows you to specify time zones in three different forms:
  
  
  A full time zone name, for example America/New_York [...]
  A time zone abbreviation, for example PST[...]
  In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset
  or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric
  offset in hours west from UTC, and DST is an optional daylight-savings
  zone abbreviation,[...]
  


Note that it never pretends to accept an ISO 8601 time zone designator. And as you found out with -0700, it doesn't. You should just use one of the forms above.

Also beware of (false) conclusions that because the parser doesn't reject a time zone, it's valid:


  One should be wary that the POSIX-style time zone feature can lead to
  silently accepting bogus input, since there is no check on the
  reasonableness of the zone abbreviations