extract epoch bug

O Hans Olav Eggestad οΏ½οΏ½οΏ½οΏ½οΏ½οΏ½ οΏ½οΏ½οΏ½οΏ½ Jul 5, 2005 :

I've just installed v8.0.3 and tested the 'epoch' part of extract, which for
my applications is fundamental. here is the result:

jova=# select extract(epoch from timestamp '19700102')\g
date_part
-----------
82800
(1 row)

jova=# select extract(epoch from timestamp '19700101')\g
date_part
-----------
-3600
(1 row)

jova=# select extract(epoch from timestamp with time zone '19700101')\g
date_part
-----------
-3600
(1 row)

jova=# select extract(epoch from timestamp without time zone '19700101')\g
date_part
-----------
-3600
(1 row)

I get pretty much of the same (with the respective 2 hours difference)
for EST TZ, on pgsql 7.4.6.

If you do

dynacom=# SET TimeZone TO UTC;
dynacom=# select extract(epoch from timestamp without time zone
'1970-01-01 00:00:00.000');
date_part
-----------
0
(1 row)

dynacom=# select extract(epoch from timestamp without time zone
'1970-01-01 00:00:04.000');
date_part
-----------
4
(1 row)

dynacom=#
dynacom=# select extract(epoch from timestamp without time zone
'1970-01-01 01:00:00.000');
date_part
-----------
3600
(1 row)

dynacom=# select extract(epoch from timestamp without time zone
'1970-01-02 00:00:00.000');
date_part
-----------
86400
(1 row)

Notice that:

dynacom=# select extract(epoch from timestamp with time zone '1970-01-01
00:00:00.000+0');
date_part
-----------
0
That might do the trick for you, altho with a first glance
i think that this should behave the same way as
the without time zone version, but it doesnt.

the time zone here is 1 hour before GMT, so i guess it has something to do with that, but i have to have this function correct. hope i can get a patch for this soon. (BTW i've not tested this in earlier versions, but i'm now going to make posgres version of my sybase applications)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
-Achilleus

olav@venus.jordforsk.no (Hans Olav Eggestad) writes:

I've just installed v8.0.3 and tested the 'epoch' part of extract, which for
my applications is fundamental. here is the result:
[ snip ]

Nothing wrong with that that I can see. If you want a date to be
interpreted as midnight GMT rather than midnight your local zone,
either say so or set TimeZone to GMT.

regards, tom lane