The following bug has been logged online:

Bug reference: 1232
Logged by: John R Pierce

Email address: pierce (AT) hogranch (DOT) com

PostgreSQL version: 7.4.2

Operating system: Linux 2.4.9

Description: Singapore Timezone missing

Details:

The timezone designation SGT is not recognized on inserts to "timestamp with
time zone" fields.

In file src/backend/utils/adt/datetime.c SGT is missing/undefined. it
should be...

{"sgt", TZ, POS(32)}, /* Singapore Time */

(i.e UTC+0800, no daylight time)



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

fyi, I am on this list, if anyone wants to tell me I'm way off base here or
whatever

something tells me I'm going to hit this problem again when our app gets
deployed at our plants in thailand and china and indonesia (and, no, I don't
yet know what time zone codes they use, I'm trying to figure out how to dump
the linux timezone files)

ok, on my redhat enterprise linux server, /usr/share/zoneinfo/Asia/Bangkok
uses ICT which is defined.

however, mainland china, ugh.

CN Asia/Shanghai east China - Beijing, Guangdong, Shanghai, etc.
CN Asia/Harbin Heilongjiang
CN Asia/Chongqing central China - Gansu, Guizhou, Sichuan, Yunnan, etc.
CN Asia/Urumqi Tibet & most of Xinjiang Uyghur
CN Asia/Kashgar southwest Xinjiang Uyghur

Asia/Shanghai Thu Aug 26 08:29:36 2004 CST
Asia/Harbin Thu Aug 26 08:29:36 2004 CST
Asia/Chongqing Thu Aug 26 08:29:36 2004 CST
Asia/Urumqi Thu Aug 26 08:29:36 2004 CST
Asia/Kashgar Thu Aug 26 08:29:36 2004 CST

so all of china uses CST, which is *NOT* CST as we know it here in North
America ;-/

*UGH*


(btw, thats the trimmed output of...
$ grep ^CN /usr/share/zoneinfo/zone.tab
and
$ cd /usr/share/zoneinfo; /usr/sbin/zdump Asia/*
)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"John R Pierce" <pierce (AT) hogranch (DOT) com> writes:
Yeah, it's going to be difficult to do much about this stuff with the
current approach of a hardwired table of zone names. There are other
unresolved conflicts (IST is one I think). The Aussies got special
dispensation (cf australian_timezones) mainly because we have several
contributors down there who were annoyed enough to code a solution.
But that approach doesn't scale. I think what we really have to do is
remove the zone name entries from the hardwired table and instead load
them from a config file. Then it would be easy for people to set up
just the names they wanted to use. This won't happen for 8.0 but maybe
in 8.1, if anyone steps up to the plate and does it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Yeah, its a maze of twisty little passages.

I just fired off a suggestion to the DBAs and SQL developers on my team that
everything should use RFC822 style time,

$ date -R
Wed, 25 Aug 2004 19:43:22 -0700

and be done with it. I haven't had a chance to look at the Java/JDBC code
which triggered the SGT bug in Singapore yet.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html