Thread: timezone problem?
Why do I get different date/time after explicitly setting timezone? This is RH Linux 5.2. test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Thu Sep 24 07:05:10 1998 JST (1 row) test=> show timezone; NOTICE: Time zone is unknown SHOW VARIABLE test=> set timezone to 'JST'; SET VARIABLE test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Wed Sep 23 22:05:10 1998 JST (1 row) -- Tatsuo Ishii
> Why do I get different date/time after explicitly setting timezone?
> This is RH Linux 5.2.
Because...
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ------------------------------
> Thu Sep 24 07:05:10 1998 JST
> test=> show timezone;
> NOTICE: Time zone is unknown
> SHOW VARIABLE
> test=> set timezone to 'JST';
> SET VARIABLE
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ------------------------------
> Wed Sep 23 22:05:10 1998 JST
On my RH-5.2 box, "JST" is not in /usr/share/zoneinfo. A non-existant
TZ evaluates to be GMT, but the system reports the string you gave
it!! I don't recall ever running across this before. But the moral of
the story is: don't do that! ;)
I'm not sure how one would check to verify that the timezone you set
is actually a valid timezone. I'd hate to restrict it to the list of
timezones Postgres knows about when parsing input (since that is a
subset of the possibilities), though that is one solution...
- Thomas
[root@golem zoneinfo]# setenv TZ HST
[root@golem zoneinfo]# date
Thu Jan 20 05:55:02 HST 2000
[root@golem zoneinfo]# setenv TZ JST
[root@golem zoneinfo]# date
Thu Jan 20 15:54:37 JST 2000
[root@golem zoneinfo]# setenv TZ GMT
[root@golem zoneinfo]# date
Thu Jan 20 15:54:45 GMT 2000
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> On my RH-5.2 box, "JST" is not in /usr/share/zoneinfo. A non-existant
> TZ evaluates to be GMT, but the system reports the string you gave
> it!! I don't recall ever running across this before.
Ugh. RedHat's not the only one: on my HPUX 10 box,
$ date
Thu Jan 20 11:13:26 EST 2000
$ TZ=GMT date
Thu Jan 20 16:13:30 GMT 2000
$ TZ=ZZZ date
Thu Jan 20 16:13:35 ZZZ 2000
$ TZ=foo date
Thu Jan 20 16:13:53 foo 2000
This may be a fairly widespread bug^H^H^Hbizarre behavior.
> I'm not sure how one would check to verify that the timezone you set
> is actually a valid timezone. I'd hate to restrict it to the list of
> timezones Postgres knows about when parsing input (since that is a
> subset of the possibilities), though that is one solution...
Well, we could solve a smaller problem: keep a list of the timezone
names we think are equivalent to GMT. Then, if we see a zero TZ offset
for any name not in the list, emit some sort of warning notice. Bit of
a kluge though.
I am not sure that this relates to Tatsuo's complaint, though.
His issue was:
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ------------------------------
> Thu Sep 24 07:05:10 1998 JST
> test=> show timezone;
> NOTICE: Time zone is unknown
If Postgres doesn't know the timezone, why is it displaying "JST" in
decoded datetimes?
Another odd thing is that I'd have expected the displayed time to be
GMT if the system doesn't know the timezone --- but the time being
shown here is 9 hours ahead of JST, not 9 hours behind... perhaps
something somewhere *does* know the local zone, but is applying the
correction backwards?
regards, tom lane
> Well, we could solve a smaller problem: keep a list of the timezone
> names we think are equivalent to GMT. Then, if we see a zero TZ offset
> for any name not in the list, emit some sort of warning notice. Bit of
> a kluge though.
Uh, yes it is :)
> I am not sure that this relates to Tatsuo's complaint, though.
> His issue was:
> > test=> select '1998-09-23 12:05:10 HST'::datetime;
> > ------------------------------
> > Thu Sep 24 07:05:10 1998 JST
> > test=> show timezone;
> > NOTICE: Time zone is unknown
> If Postgres doesn't know the timezone, why is it displaying "JST" in
> decoded datetimes?
"Time zone is unknown" is the usual state if there is not an explicit
SET TIME ZONE by a client. Doesn't mean anything more, and doesn't
imply that the backend can't do timezone stuff. Postgres relies on
system-supplied routines if the year is between 1903 and 2038 (mas o
menos; I didn't look it up).
> Another odd thing is that I'd have expected the displayed time to be
> GMT if the system doesn't know the timezone --- but the time being
> shown here is 9 hours ahead of JST, not 9 hours behind... perhaps
> something somewhere *does* know the local zone, but is applying the
> correction backwards?
HST is interpreted by Postgres as Hawaii Standard Time, which is on
the other side of the date line from Japan. Planning a vacation
Tatsuo?? :))
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Thu, 20 Jan 2000, Tom Lane wrote: > Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > > On my RH-5.2 box, "JST" is not in /usr/share/zoneinfo. A non-existant > > TZ evaluates to be GMT, but the system reports the string you gave > > it!! I don't recall ever running across this before. > > Ugh. RedHat's not the only one: on my HPUX 10 box, > > $ date > Thu Jan 20 11:13:26 EST 2000 > $ TZ=GMT date > Thu Jan 20 16:13:30 GMT 2000 > $ TZ=ZZZ date > Thu Jan 20 16:13:35 ZZZ 2000 > $ TZ=foo date > Thu Jan 20 16:13:53 foo 2000 > > This may be a fairly widespread bug^H^H^Hbizarre behavior. Odd. Here's how FreeBSD acts: $ TZ=GMT date Thu Jan 20 16:47:29 GMT 2000 $ TZ=foo date Thu Jan 20 16:47:36 GMT 2000 $ TZ=EDT date Thu Jan 20 16:47:47 GMT 2000 $ TZ=EST date Thu Jan 20 11:47:54 EST 2000 $ TZ=PST date Thu Jan 20 16:48:03 GMT 2000 $ TZ=ZZZ date Thu Jan 20 16:48:09 GMT 2000 $ TZ=JST date Thu Jan 20 16:49:00 GMT 2000 $ TZ=MST date Thu Jan 20 09:50:05 MST 2000 $ TZ=CST date Thu Jan 20 16:49:32 GMT 2000 Strange, it does MST and EST but not CST and PST. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/moor less at Pop4 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> HST is interpreted by Postgres as Hawaii Standard Time, which is on
> the other side of the date line from Japan. Planning a vacation
> Tatsuo?? :))
Then there's still something wrong:
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ------------------------------
> Wed Sep 23 22:05:10 1998 JST
10 hours behind JST (= GMT+9, IIRC) is in the wrong ocean to be
Hawaii...
regards, tom lane
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > > HST is interpreted by Postgres as Hawaii Standard Time, which is on > > the other side of the date line from Japan. Planning a vacation > > Tatsuo?? :)) I wish I could do so:-) I hate the cold winter in Japan... > Then there's still something wrong: > > > test=> select '1998-09-23 12:05:10 HST'::datetime; > > ------------------------------ > > Wed Sep 23 22:05:10 1998 JST > > 10 hours behind JST (= GMT+9, IIRC) is in the wrong ocean to be > Hawaii... Right. HST is GMT-10, and JST - HST = 19 hours. So '1998-09-23 12:05:10 HST' shoud be 'Thu Sep 24 07:05:10 1998 JST', rather than 'Wed Sep 23 22:05:10 1998 JST'... Looking into the zoneinfo files under /usr/share/zoneinfo, I found 'Japan' as a valid zone name (I could not find 'JST' too on my RH box). test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Thu Sep 24 07:05:10 1998 JST -- correct (1 row) test=> set timezone to 'JST'; SET VARIABLE test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Wed Sep 23 22:05:10 1998 JST -- wrong. seems interpreted as GMT (UTC) (1 row) test=> set timezone to 'Japan'; SET VARIABLE test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Thu Sep 24 07:05:10 1998 JST -- correct. but why showed as JST? (1 row) test=> reset timezone; RESET VARIABLE test=> select '1998-09-23 12:05:10 HST'::datetime; ?column? ------------------------------Thu Sep 24 07:05:10 1998 JST -- again, correct (1 row) Seems something wrong with my RH 5.2. Note that FreeBSD does have the problem. -- Tatsuo Ishii
> Odd. Here's how FreeBSD acts:
> Strange, it does MST and EST but not CST and PST.
Try PST8PDT for the Pacific TZ and CST6CDT for Central time. Not sure
why the zinc databases have entries for EST and MST as well as for
EST5EDT and MST7MDT (at least on my RH-5.2 linux box).
I like the behavior that it prints GMT when given an invalid time
zone; that is actually the behavior I recall when testing this a year
or two ago. Something changed/improved/broke in the meantime with some
of these boxes...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Tatsuo Ishii wrote:
>
> > Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > > HST is interpreted by Postgres as Hawaii Standard Time, which is on
> > > the other side of the date line from Japan. Planning a vacation
> > > Tatsuo?? :))
>
> I wish I could do so:-) I hate the cold winter in Japan...
>
> > Then there's still something wrong:
> >
> > > test=> select '1998-09-23 12:05:10 HST'::datetime;
> > > ------------------------------
> > > Wed Sep 23 22:05:10 1998 JST
> >
> > 10 hours behind JST (= GMT+9, IIRC) is in the wrong ocean to be
> > Hawaii...
>
> Right. HST is GMT-10, and JST - HST = 19 hours. So '1998-09-23
> 12:05:10 HST' shoud be 'Thu Sep 24 07:05:10 1998 JST', rather than 'Wed
> Sep 23 22:05:10 1998 JST'...
>
> Looking into the zoneinfo files under /usr/share/zoneinfo, I found 'Japan'
> as a valid zone name (I could not find 'JST' too on my RH box).
>
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ?column?
> ------------------------------
> Thu Sep 24 07:05:10 1998 JST -- correct
> (1 row)
>
> test=> set timezone to 'JST';
> SET VARIABLE
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ?column?
> ------------------------------
> Wed Sep 23 22:05:10 1998 JST -- wrong. seems interpreted as GMT (UTC)
> (1 row)
>
> test=> set timezone to 'Japan';
> SET VARIABLE
> test=> select '1998-09-23 12:05:10 HST'::datetime;
> ?column?
> ------------------------------
> Thu Sep 24 07:05:10 1998 JST -- correct. but why showed as JST?
> (1 row)
That is typical when you use the long form of the time zone name such
as "Japan". You will also find a "US/Pacific" on your machine:
[root@golem zoneinfo]# setenv TZ US/Pacific
[root@golem zoneinfo]# date
Thu Jan 20 21:24:24 PST 2000
which is the same as PST8PDT.
In /usr/share/zoneinfo/US, the mysteries of the various states'
conventions are revealed:
[root@golem zoneinfo]# ls -1 US
Alaska
Aleutian
Arizona
Central
East-Indiana
Eastern
Hawaii
Indiana-Starke
Michigan
Mountain
Pacific
Samoa
where, as Vince pointed out, Indiana, Michigan, and Arizona seem to be
special cases within the usual three timezones.
> Seems something wrong with my RH 5.2. Note that FreeBSD does have the
> problem.
Sorry, FreeBSD also has the problem, or does not??
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> That is typical when you use the long form of the time zone name such > as "Japan". You will also find a "US/Pacific" on your machine: Maybe I'm going to check where the translation Japan -> JST has benn actually done. > > Seems something wrong with my RH 5.2. Note that FreeBSD does have the > > problem. > > Sorry, FreeBSD also has the problem, or does not?? Sorry, FreeBSD does *not* have the problem as far as I know. -- Tatsuo Ishii
> Maybe I'm going to check where the translation Japan -> JST has benn
> actually done.
You will find it in the timezone file itself. Use "zdump" to look at
the file of interest:
[root@golem zoneinfo]# zdump -v /usr/share/zoneinfo/Japan
Japan Fri Dec 13 20:45:52 1901 GMT = Sat Dec 14 05:45:52 1901 JST
isdst=0
Japan Sat Dec 14 20:45:52 1901 GMT = Sun Dec 15 05:45:52 1901 JST
isdst=0
Japan Mon Jan 18 03:14:07 2038 GMT = Mon Jan 18 12:14:07 2038 JST
isdst=0
Japan Tue Jan 19 03:14:07 2038 GMT = Tue Jan 19 12:14:07 2038 JST
isdst=0
Wow, that is a short set of rules! The PST8PDT file is 374 lines ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Has anyone noticed the following timezoning problem.. If a datetime variable is read out, and then inserted back in again (verbatim) I get a change in the time value. I suspect that it because out lime zona Australia/Adelaide is CST, which I belive is also an American timezone. Trimming the timezone info (CST) off, fixes this problem. Can anyone shed any light? How does one get the +1030 timezone format? PaulS > > Maybe I'm going to check where the translation Japan -> JST has benn > > actually done. > > You will find it in the timezone file itself. Use "zdump" to look at > the file of interest: > > [root@golem zoneinfo]# zdump -v /usr/share/zoneinfo/Japan > Japan Fri Dec 13 20:45:52 1901 GMT = Sat Dec 14 05:45:52 1901 JST > isdst=0 > Japan Sat Dec 14 20:45:52 1901 GMT = Sun Dec 15 05:45:52 1901 JST > isdst=0 > Japan Mon Jan 18 03:14:07 2038 GMT = Mon Jan 18 12:14:07 2038 JST > isdst=0 > Japan Tue Jan 19 03:14:07 2038 GMT = Tue Jan 19 12:14:07 2038 JST > isdst=0 > > Wow, that is a short set of rules! The PST8PDT file is 374 lines ;) > > - Thomas > > -- > Thomas Lockhart lockhart@alumni.caltech.edu > South Pasadena, California > > ************ >
> If a datetime variable is read out, and then inserted back in again
> (verbatim) I get a change in the time value. I suspect that it because
> out lime zona Australia/Adelaide is CST, which I belive is also an
> American timezone. Trimming the timezone info (CST) off, fixes this
> problem. Can anyone shed any light?
Yup. Fully 1/4 of our timezone lookup table is consumed by Australian
time zones (y'all have multiple names for *everything*!). There are
some name conflicts, of course :(
> How does one get the +1030 timezone format?
Use ACSST or CADT or SADT (at least that is what is defined in the
Postgres lookup table for *exactly* the same time offset).
Or...
Apply the enclosed patch, then compile the backend with:
-DUSE_AUSTRALIAN_RULES=1
(Or move to another country. Recompiling the backend is probably
easier... ;)
This is covered in the docs in the appendix on "Date/Time Support",
but CST was not included and it looks to me that EAST had sign
trouble. Both are fixed in the enclosed patch.
btw, the patch also tries to fix the "GMT+hhmm" timezone format
reported recently as being available on FreeBSD; perhaps someone could
test that at the same time.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California*** dt.c.orig Tue Jan 18 17:25:51 2000
--- dt.c Fri Jan 21 07:12:10 2000
***************
*** 1980,1986 ****
--- 1980,1990 ----
{"cdt", DTZ, NEG(30)}, /* Central Daylight Time */
{"cet", TZ, 6}, /* Central European Time */
{"cetdst", DTZ, 12}, /* Central European Dayl.Time */
+ #if USE_AUSTRALIAN_RULES
+ {"cst", TZ, 63}, /* Australia Eastern Std Time */
+ #else
{"cst", TZ, NEG(36)}, /* Central Standard Time */
+ #endif
{DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
{"dec", MONTH, 12},
{"december", MONTH, 12},
***************
*** 1988,1994 ****
{"dow", RESERV, DTK_DOW}, /* day of week */
{"doy", RESERV, DTK_DOY}, /* day of year */
{"dst", DTZMOD, 6},
! {"east", TZ, NEG(60)}, /* East Australian Std Time */
{"edt", DTZ, NEG(24)}, /* Eastern Daylight Time */
{"eet", TZ, 12}, /* East. Europe, USSR Zone 1 */
{"eetdst", DTZ, 18}, /* Eastern Europe */
--- 1992,1998 ----
{"dow", RESERV, DTK_DOW}, /* day of week */
{"doy", RESERV, DTK_DOY}, /* day of year */
{"dst", DTZMOD, 6},
! {"east", TZ, 60}, /* East Australian Std Time */
{"edt", DTZ, NEG(24)}, /* Eastern Daylight Time */
{"eet", TZ, 12}, /* East. Europe, USSR Zone 1 */
{"eetdst", DTZ, 18}, /* Eastern Europe */
***************
*** 2688,2693 ****
--- 2692,2712 ----
if (DecodeTimezone(field[i], tzp) != 0)
return -1;
tmask = DTK_M(TZ);
+
+ /*
+ * Swallow an immediately previous timezone if it is GMT
+ * This handles the odd case in FreeBSD of "GMT+0800"
+ * but note that we need to flip the sign on this too.
+ * Claims to be some sort of Posix standard format :(
+ * - thomas 2000-01-20
+ */
+ if ((tmask & fmask) && (tzp != NULL) && (*tzp == 0)
+ && ((*field[i] == '+') || (*field[i] == '-'))
+ && (i >= 1) && (strcasecmp(field[i-1], "GMT") == 0))
+ {
+ tmask &= ~DTK_M(TZ);
+ *tzp = -(*tzp);
+ }
break;
case DTK_NUMBER:
>> If a datetime variable is read out, and then inserted back in again >> (verbatim) I get a change in the time value. I suspect that it because >> out lime zona Australia/Adelaide is CST, which I belive is also an >> American timezone. Trimming the timezone info (CST) off, fixes this >> problem. Can anyone shed any light? Yes, and even worse, CST also is "China Standard Time" in some operating systems. I won't go into how broken every operating system is vis-a-vis Chinese timezones (but, believe me, it's a mess). >From here on out, I'm strictly in "+0800". >Yup. Fully 1/4 of our timezone lookup table is consumed by Australian >time zones (y'all have multiple names for *everything*!). There are >some name conflicts, of course :( I've become convinced that any project that thinks it is going to keep comprehensive, accurate, non-conflicting, non-obsolete timezone information in an application-specific table is woefully misguided. >btw, the patch also tries to fix the "GMT+hhmm" timezone format >reported recently as being available on FreeBSD; perhaps someone could >test that at the same time. Does this patch apply cleanly against 6.5.3? -Michael Robinson
> Yes, and even worse, CST also is "China Standard Time" in some operating
> systems. I won't go into how broken every operating system is vis-a-vis
> Chinese timezones (but, believe me, it's a mess).
> >From here on out, I'm strictly in "+0800".
> I've become convinced that any project that thinks it is going to keep
> comprehensive, accurate, non-conflicting, non-obsolete timezone information
> in an application-specific table is woefully misguided.
Yup. And that brings up an issue: I would like to have the *default*
style for date/time output in 7.0 be ISO, rather than the current
"traditional Postgres". I was waiting for a major rev to do this (but
it probably should have happened before the y2k change of year). It's
a one-liner to update this.
Bruce, can you add this to the "critical items" for 7.0, barring fatal
objections from other developers?
> >btw, the patch also tries to fix the "GMT+hhmm" timezone format
> >reported recently as being available on FreeBSD; perhaps someone could
> >test that at the same time.
> Does this patch apply cleanly against 6.5.3?
I'm not certain, but it should since this area of the code does not
change very much. If you apply with
cd src/backend/utils/adt
patch < dt.c.patch
you should get a dt.c.orig so can revert easily if necessary.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On 21-Jan-00 Thomas Lockhart wrote: > > In /usr/share/zoneinfo/US, the mysteries of the various states' > conventions are revealed: > > [root@golem zoneinfo]# ls -1 US > Alaska > Aleutian > Arizona > Central > East-Indiana > Eastern > Hawaii > Indiana-Starke > Michigan > Mountain > Pacific > Samoa > > where, as Vince pointed out, Indiana, Michigan, and Arizona seem to be > special cases within the usual three timezones. Michigan isn't a special case. We're EST5EDT, I never did figure out why we're listed in there. Perhaps we were among the first to fully implement DST? I know I remember we were doing it in a test case long before it went on the ballot in the state (which was controversial in itself). Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/moor less at Pop4 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================