i'm seeing some confusing results with the datetime function.
with TIMEZONE set to its default value (which for me is "EST"), i see the
following:
select datetime (0);
timestamp
---------------------
1970-01-01 05:00:00
(1 row)
i would have expected to see "1969-12-31 19:00:00". which is my local time
(EST = -5:00) at the start of the epoch. so the result is off by 10 hours.
i'm having a lot of trouble understanding that result.
here's a concise example which illustrates the problem:
select date_part('epoch', datetime (0));
date_part
-----------
18000
(1 row)
the cheetah perl/dbi manual is fairly explicit in saying that the
date_part('epoch', <>) and datetime(<>) are supposed to be inverses of each
other, though the above example shows this is most definitely not the case.
with local timezone set to GMT, this works as expected (above example
returns 0), however i can not see how the local timezone should be getting
involved in this computation - i must be missing something...
perhaps the datetime function is trying to convert the value passed to it
from local time (adding 5 hours to it). this explains the second result
(18000), but doesn't seem explain the first. this is also not what i was
looking for from the datetime function, and would seem to be at odds with
the perl/dbi manual.
can someone help me out by explaining this? is datetime depricated (i
can't find it in the online manual)? if so, what is the right function to
convert an integer value representing seconds since the epoch to a postgres
time value? ...preferably without regard to timezone -- i.e., the argument
is always treated as a number of seconds time since the 0:00 1/1/1970 GMT?
thanks.
