Re: Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)

On Tue, Mar 25, 2008 at 07:50:30PM -0000, David Rowley wrote:
> Hi,
>
> I came across something weird that I personally can't explain regarding the
> EXTRACT function.
> I've created a few SQLs to let people see what is happening. Perhaps there
> is an explaination for it.
>
> Here is my script to test:
>
>
> -- The first column of the following 2 queries is trying to
> -- calculate the number of days since Jan 1st 1970. The date
> -- 2007-04-09 seems to be special because the date seems to change
> -- at 1am rather than at mid night as I would expect it to.
> -- SHOW ALL shows my TimeZone is set to "Europe/London"
>
> SELECT FLOOR(EXTRACT(EPOCH FROM '2007-04-09 00:59:59'::TIMESTAMP WITHOUT
> TIME ZONE) / 86400.0),EXTRACT(EPOCH FROM '2007-04-09 00:59:59'::TIMESTAMP
> WITHOUT TIME ZONE);

This is a bug. Extract(epoch from [timestamp without time zone])
shouldn't work at all.  Epoch only has meaning in the context of a
timestamptz.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate