BUG #13691: Postgres reverse timezone system
The following bug has been logged on the website:
Bug reference: 13691
Logged by: Đặng Minh Dũng
Email address: dungdm93@live.com
PostgreSQL version: 9.4.4
Operating system: Microsoft Windows 7
Description:
I'm in Vietnam with timezone ICT (GMT+7).
I wanna find the best way to convert datetime between timezone like this:
postgres=# SELECT
postgres-# now() AS "NOW",
postgres-# now() AT TIME ZONE 'ICT' AS "ICT",
postgres-# now() AT TIME ZONE '+7:00' AS "+7:00",
postgres-# now() AT TIME ZONE 'UTC+7' AS "UTC+7",
postgres-# now() AT TIME ZONE 'UTC+7:00' AS "UTC+7:00",
postgres-# now() AT TIME ZONE 'GMT+7:00' AS "GMT+7:00";
-[ RECORD 1 ]------------------------
NOW | 2015-10-21 09:55:50.631+07
ICT | 2015-10-21 09:55:50.631
+7:00 | 2015-10-20 19:55:50.631
UTC+7 | 2015-10-20 19:55:50.631
UTC+7:00 | 2015-10-20 19:55:50.631
GMT+7:00 | 2015-10-20 19:55:50.631
As you can see, ICT and GMT+7:00 or +7:00 are identical, Unfortunately, the
query results are different. However, when i select:
SELECT now() AT TIME ZONE '-7:00' AS "-7:00";
The result is correct. So why Postgres reverse timezone system???
Thanks you.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
dungdm93@live.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
As some wise man once said, the great thing about standards is there are
so many to choose from :-(
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Oct 21, 2015 at 6:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
dungdm93@live.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thomas Munro <thomas.munro@enterprisedb.com> writes:
It's pretty surprising that '+13' is not negated, but '+13:00' is. Is
that expected?
Um, well, there's yet a third randomly-different standard involved here,
which is that the SQL spec says that timezones can be specified as purely
numeric GMT offsets --- using the ISO sign convention. So "+13" is
captured by that rule; while "+13:00" doesn't look like a plain number
so it gets taken in by the POSIX conventions.
We can probably find a few more standards governing PG's behavior in
this area, if you care to keep poking ;-). But looking for absolute
mathematical consistency in anything having to do with timekeeping is
a lost cause.
Since there pretty much isn't any way that the SQL spec's timezone
rules don't suck, I'd urge avoiding that particular notation. Really
the Olson-style geographically-based zone names are the least ambiguous
and the least likely to be wrong when considering past and future law
changes. If I were the OP I'd be using "Asia/Ho_Chi_Minh".
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs