Re: BUG #14082: Unexpected time adjustment for output using "at time zone" - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #14082: Unexpected time adjustment for output using "at time zone"
Date
Msg-id 13349.1460414959@sss.pgh.pa.us
Whole thread Raw
In response to BUG #14082: Unexpected time adjustment for output using "at time zone"  (duncan.stokes@eyemagnet.com)
List pgsql-bugs
duncan.stokes@eyemagnet.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

pgsql-bugs by date:

Previous
From: duncan.stokes@eyemagnet.com
Date:
Subject: BUG #14082: Unexpected time adjustment for output using "at time zone"
Next
From: Fabien COELHO
Date:
Subject: Re: [HACKERS] Breakage with VACUUM ANALYSE + partitions