Обсуждение: more timeofday() and "set time zone" weirdness

I posted earlier with a very complex example.  This simple one
gets to the point much faster.  timeofday() seems to behave
inconsistently when the timezone is set with "GMT+X" notation.

foo=> select cast(timeofday() as timestamp with time zone); set time
zone 'GMT+4'; select cast(timeofday() as timestamp with time zone);
           timeofday
-------------------------------
 2003-08-13 11:01:26.502064-04
(1 row)

SET VARIABLE
           timeofday
-------------------------------
 2003-08-13 07:01:26.503328-04
(1 row)


I am currently 4 hours behind GMT.  The first timestamp reflects
the correct time.  After setting the time zone to "GMT+4", I get
a time value that is 4 hours too early.  If I were to instead
use "America/New_York" for the time zone, I would not have this
problem.  Unfortunately, I cannot use such notation under Cygwin.
BTW -- I am seeing this problem with version 7.2.3 under RH Linux
version 7.3.

Jason Priebe
jason_priebe@yahoo.com

jason_priebe@yahoo.com (Jason Priebe) writes:
> foo=> select cast(timeofday() as timestamp with time zone); set time
> zone 'GMT+4'; select cast(timeofday() as timestamp with time zone);

This breaks because localtime() is broken: it returns the string "GMT"
as the time zone.  You can see the problem without any reference to
Postgres:

$ date
Wed Aug 13 16:09:56 EDT 2003
$ TZ='GMT+4' date
Wed Aug 13 16:09:57 GMT 2003
$

My advice: don't specify the time zone like that.  Use 'EST5EDT' or some
other standard timezone specifier.  Or avoid timeofday() ... now() or
current_timestamp would be better choices anyway ...

            regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> jason_priebe@yahoo.com (Jason Priebe) writes:
> > foo=> select cast(timeofday() as timestamp with
> time zone); set time
> > zone 'GMT+4'; select cast(timeofday() as timestamp
> with time zone);
>
> This breaks because localtime() is broken: it
> returns the string "GMT"
> as the time zone.  You can see the problem without
> any reference to
> Postgres:
>
> $ date
> Wed Aug 13 16:09:56 EDT 2003
> $ TZ='GMT+4' date
> Wed Aug 13 16:09:57 GMT 2003
> $
>
> My advice: don't specify the time zone like that.
> Use 'EST5EDT' or some
> other standard timezone specifier.  Or avoid
> timeofday() ... now() or
> current_timestamp would be better choices anyway ...

Agreed.  My problem was that under Cygwin, I was
not able to use any timezone specifications other than
the GMT+X notation.  However, I did find a fix for
this problem:  I tarred up my /usr/share/zoneinfo
files from RH Linux 7.3, and plopped them down into
the Cygwin filesystem, and voila, I was able to use
the "nice" timezone notations, like
"America/New_York".

Specifically, I had to do this:

SET TIME ZONE '/usr/share/zoneinfo/America/New_York'

For some reason, I had to use the entire path to
the file, rather than just 'America/New_York' like
I would do under Linux.  Maybe this little tidbit will
help another poor Windows user.  Believe me, if I
could
use Linux for this application, I would.

Thanks for taking the time to respond to my barrage
of questions yesterday.

-Jason Priebe

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com