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
A time zone abbreviation, for example
In addition to the timezone names and abbreviations, PostgreSQL will
accept POSIX-style time zone specifications of the form
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
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'
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 input. It just re-bases the time literal on a different offset. This is different from its use with
timestamptz input, where the data type is also switched.
But do not use the data type
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: