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)

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 (AT) postgresql (DOT) org)

O Hans Olav Eggestad Ýãñáøå óôéò Jul 5, 2005 :

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.


--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

olav (AT) venus (DOT) jordforsk.no (Hans Olav Eggestad) writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings