Re: BUG #14082: Unexpected time adjustment for output using "at time zone"

duncan(dot)stokes(at)eyemagnet(dot)com writes:
> For the following data:
> datetimetest=# SELECT * FROM datetimetest_log;
> id | datetime | comment
> ----+------------------------+---------
> 1 | 2016-04-11 09:51:35+12 | Test #1
> 2 | 2016-04-11 09:51:37+12 | Test #2
> (2 rows)

> Get the following responses:
> datetimetest=# SELECT datetime AT TIME ZONE INTERVAL '+12:00' FROM
> datetimetest_log;
> timezone
> ---------------------
> 2016-04-11 09:51:35
> 2016-04-11 09:51:37
> (2 rows)

> datetimetest=# SELECT datetime AT TIME ZONE '+12' FROM datetimetest_log;
> timezone
> ---------------------
> 2016-04-10 09:51:35 ** WRONG DATE FOR +12 ZONE **
> 2016-04-10 09:51:37 ** WRONG DATE FOR +12 ZONE **
> (2 rows)

No, it isn't wrong. A time zone specified by an INTERVAL constant
follows the ISO8601 convention that positive offsets are east of
Greenwich; and that's also the convention we use when displaying
timestamptz values, so your first two sets of results match up.
In time zones specified by name, we follow the POSIX convention
(also used by the IANA timezone folk) in which positive offsets are
west of Greenwich. The great thing about standards is there are
so many to choose from :-(

> Having read the documentation (section 8.5.3), I can't actually see any
> allowance for this formatted timezone (e.g. +12 or -12).

It's a POSIX timezone specification with an empty zone abbreviation
and no DST part.

regards, tom lane