Oddities with AT TIME ZONE and UTC offsets

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?

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:

• "AT TIME ZONE" with zone name PostgreSQL bug?

• Ignoring timezones altogether in Rails and PostgreSQL

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:

• Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

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:

• What is a valid use case for using TIME WITH TIME ZONE?

StackExchange Database Administrators Q#160065, answer score: 12