4

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.

8

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

  • Thank you, I checked the AT TIME ZONE documentation, but didn't think to check the date/time documentation. I'll just stick to the named time zones for my own sanity. – Villiers Strauss Feb 29 '16 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.