Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)

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);
SELECT FLOOR(EXTRACT(EPOCH FROM '2007-04-09 01:00:00'::TIMESTAMP WITHOUT
TIME ZONE) / 86400.0),EXTRACT(EPOCH FROM '2007-04-09 01:00:00'::TIMESTAMP
WITHOUT TIME ZONE);

-- The following query converts '2007-04-09 00:59:59' into seconds since
EPOCH then back to timestamp
-- The timestamp loses 1 hour in the conversion

SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + EXTRACT(EPOCH FROM '2007-04-09
00:59:59'::TIMESTAMP WITHOUT TIME ZONE) * INTERVAL '1 second';

-- For me this query returns '2007-04-08 23:59:59' where the input is
'2007-04-09 00:59:59' (one hour earlier)

-- Is this down to daylight saving? Daylight saving changes at the end of
march in my time zone.


test=# SELECT VERSION();
                       version
-----------------------------------------------------
 PostgreSQL 8.3.0, compiled by Visual C++ build 1400


If anyone is able to give me some information about this it would be most
helpful.

David.