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.
