Thread: BUG #1232: Singapore Timezone missing

The following bug has been logged online:

Bug reference:      1232
Logged by:          John R Pierce

Email address:      pierce@hogranch.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)

> The following bug has been logged online:
...
> The timezone designation SGT is not recognized on inserts to "timestamp
> with
> time zone" fields.
...


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/*
)

"John R Pierce" <pierce@hogranch.com> writes:
> so all of china uses CST, which is *NOT* CST as we know it here in North
> America ;-/

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

>> so all of china uses CST, which is *NOT* CST as we know it here in North
>> America ;-/
>
> 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).

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.