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

Thu, 20 Jan 2000 12:49:50 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
timezone problem?
> 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

Thu, 20 Jan 2000 16:01:03 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?
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

Thu, 20 Jan 2000 11:29:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] timezone problem? 
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/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Thu, 20 Jan 2000 11:51:29 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] timezone problem? 
> 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

Thu, 20 Jan 2000 16:52:47 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?
> 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

Thu, 20 Jan 2000 17:24:40 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?
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

Thu, 20 Jan 2000 17:42:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] timezone problem? 
> 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


Fri, 21 Jan 2000 10:56:51 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] timezone problem? 
> 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

Fri, 21 Jan 2000 14:25:41 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] timezone problem?
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

Fri, 21 Jan 2000 05:28:44 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?
> 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

Fri, 21 Jan 2000 05:47:49 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?
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
> 
> ************
> 


Fri, 21 Jan 2000 16:28:43 +1030 (CST)
Paul Schulz <pauls@caemrad.com.au>
Re: [HACKERS] timezone 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?

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
Fri, 21 Jan 2000 07:16:55 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone 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?

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



Fri, 21 Jan 2000 16:39:00 +0800 (+0800)
Michael Robinson <robinson@netrinsics.com>
Re: [HACKERS] timezone problem?
> 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

Fri, 21 Jan 2000 14:14:36 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] timezone problem?

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/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================



Fri, 21 Jan 2000 11:23:40 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] timezone problem?