Re: BUG #13691: Postgres reverse timezone system

On Wed, Oct 21, 2015 at 6:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> dungdm93(at)live(dot)com writes:
>> The result is correct. So why Postgres reverse timezone system???
>
> The problem is the conflict between POSIX-style time zone naming (in which
> positive GMT offsets are west of Greenwich) and ISO-8601 time zone
> notation (in which positive GMT offsets are east of Greenwich). We follow
> the ISO spec for data value input and output, but time zone names follow
> the POSIX convention because that's what the Olson/IANA time zone database
> does. So the zone you are after is called "UTC-7" not "UTC+7". There is
> more info in the "Time Zones" section of our manual:
> http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES

It's pretty surprising that '+13' is not negated, but '+13:00' is. Is
that expected?

postgres=# set timezone = '+13';
SET
postgres=# select now();
┌───────────────────────────────┐
│ now │
├───────────────────────────────┤
│ 2015-10-21 18:17:09.460947+13 │
└───────────────────────────────┘
(1 row)

postgres=# set timezone = '+13:00';
SET
postgres=# select now();
┌───────────────────────────────┐
│ now │
├───────────────────────────────┤
│ 2015-10-20 16:17:13.373043-13 │
└───────────────────────────────┘
(1 row)

--
Thomas Munro
http://www.enterprisedb.com