Re: 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(at)postgresql(dot)org)
>

--
-Achilleus