Thread: 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.

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

T24gV2VkLCBPY3QgMjEsIDIwMTUgYXQgNjoxNSBQTSwgVG9tIExhbmUgPHRnbEBzc3MucGdoLnBh
LnVzPiB3cm90ZToNCj4gZHVuZ2RtOTNAbGl2ZS5jb20gd3JpdGVzOg0KPj4gVGhlIHJlc3VsdCBp
cyBjb3JyZWN0LiBTbyB3aHkgUG9zdGdyZXMgcmV2ZXJzZSB0aW1lem9uZSBzeXN0ZW0/Pz8NCj4N
Cj4gVGhlIHByb2JsZW0gaXMgdGhlIGNvbmZsaWN0IGJldHdlZW4gUE9TSVgtc3R5bGUgdGltZSB6
b25lIG5hbWluZyAoaW4gd2hpY2gNCj4gcG9zaXRpdmUgR01UIG9mZnNldHMgYXJlIHdlc3Qgb2Yg
R3JlZW53aWNoKSBhbmQgSVNPLTg2MDEgdGltZSB6b25lDQo+IG5vdGF0aW9uIChpbiB3aGljaCBw
b3NpdGl2ZSBHTVQgb2Zmc2V0cyBhcmUgZWFzdCBvZiBHcmVlbndpY2gpLiAgV2UgZm9sbG93DQo+
IHRoZSBJU08gc3BlYyBmb3IgZGF0YSB2YWx1ZSBpbnB1dCBhbmQgb3V0cHV0LCBidXQgdGltZSB6
b25lIG5hbWVzIGZvbGxvdw0KPiB0aGUgUE9TSVggY29udmVudGlvbiBiZWNhdXNlIHRoYXQncyB3
aGF0IHRoZSBPbHNvbi9JQU5BIHRpbWUgem9uZSBkYXRhYmFzZQ0KPiBkb2VzLiAgU28gdGhlIHpv
bmUgeW91IGFyZSBhZnRlciBpcyBjYWxsZWQgIlVUQy03IiBub3QgIlVUQys3Ii4gIFRoZXJlIGlz
DQo+IG1vcmUgaW5mbyBpbiB0aGUgIlRpbWUgWm9uZXMiIHNlY3Rpb24gb2Ygb3VyIG1hbnVhbDoN
Cj4gaHR0cDovL3d3dy5wb3N0Z3Jlc3FsLm9yZy9kb2NzLzkuNC9zdGF0aWMvZGF0YXR5cGUtZGF0
ZXRpbWUuaHRtbCNEQVRBVFlQRS1USU1FWk9ORVMNCg0KSXQncyBwcmV0dHkgc3VycHJpc2luZyB0
aGF0ICcrMTMnIGlzIG5vdCBuZWdhdGVkLCBidXQgJysxMzowMCcgaXMuICBJcw0KdGhhdCBleHBl
Y3RlZD8NCg0KcG9zdGdyZXM9IyBzZXQgdGltZXpvbmUgPSAnKzEzJzsNClNFVA0KcG9zdGdyZXM9
IyBzZWxlY3Qgbm93KCk7DQrilIzilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDi
lIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDi
lJANCuKUgiAgICAgICAgICAgICAgbm93ICAgICAgICAgICAgICDilIINCuKUnOKUgOKUgOKUgOKU
gOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKU
gOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUpA0K4pSCIDIwMTUtMTAtMjEgMTg6MTc6MDkuNDYw
OTQ3KzEzIOKUgg0K4pSU4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA
4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSYDQoo
MSByb3cpDQoNCnBvc3RncmVzPSMgc2V0IHRpbWV6b25lID0gJysxMzowMCc7DQpTRVQNCnBvc3Rn
cmVzPSMgc2VsZWN0IG5vdygpOw0K4pSM4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA
4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA
4pSA4pSQDQrilIIgICAgICAgICAgICAgIG5vdyAgICAgICAgICAgICAg4pSCDQrilJzilIDilIDi
lIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDi
lIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilKQNCuKUgiAyMDE1LTEwLTIwIDE2OjE3OjEz
LjM3MzA0My0xMyDilIINCuKUlOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKU
gOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKU
mA0KKDEgcm93KQ0KDQotLSANClRob21hcyBNdW5ybw0KaHR0cDovL3d3dy5lbnRlcnByaXNlZGIu
Y29tDQo=

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