PostgreSQL – Oddities with AT TIME ZONE and UTC Offsets

postgresqltimestamptimezoneutc-time

I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result:

select timezone('America/Chicago', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC'),
       timezone('UTC-6'          , '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC');

However, the result is:

2017-01-01 06:00:00 | 2017-01-01 18:00:00

Also, this behavior is highly awkward,

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1'; 
  timezone   
-------------
 03:00:00+01

Can anyone explain this?

Best Answer

A time zone name carries more information than an abbreviation or a simple time zone offset. 'UTC-6' is a "POSIX-style time zone specification" which is just an abbreviation plus offset.
The manual on Time Zones:

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, assumed to stand for one hour ahead of the given offset.

The difference you observe stems from another oddity. You have to use + instead of -:

timezone('UTC+6', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC')

The manual again:

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.

However, even after fixing the offset error, both expressions are still not equivalent. Among other things, a time zone name like 'America/Chicago' also considers rules for daylight saving time (DST).

BTW, your expression can be simplified to:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'UTC+6'

But you probably want the time zone name, to be safe:

timestamptz '2017-01-01 12:00:00 +0' AT TIME ZONE 'America/Chicago'

Related:

To address your second example:

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1';

Simplified, equivalent syntax:

SELECT timetz '1:00 -1' AT TIME ZONE '-1';

The two instances of the literal -1 have different meaning. The first offset is part of the *timetz* literal signifying a location east of Greenwich (complying to the SQL standard). The second is a POSIX-style time zone specifications signifying an offset west of Greenwich. In the absence of a zone specification, UTC is assumed as base. See:

Also note that the AT TIME ZONE construct returns timetz for timetz input. It just re-bases the time literal on a different offset. This is different from its use with timestamp / timestamptz input, where the data type is also switched.

But do not use the data type timetz (time with time zone) at all. It's broken by design and only included in Postgres because it's part of standard SQL. It's use is explicitly discouraged. See:

Related Question