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