Thread: 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.
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
David Fetter <david@fetter.org> writes:
> On Tue, Mar 25, 2008 at 07:50:30PM -0000, David Rowley wrote:
>> 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.
One man's bug is another man's feature ;-). The EPOCH code is designed
to produce the same result as if you had casted the timestamp to
timestamp with timezone --- the important point there being that the
stamp will be interpreted as being in your local time zone (per the
TimeZone parameter). So the problem with the OP's example is that he's
doing
SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + EXTRACT(EPOCH FROM '2007-04-09
00:59:59'::TIMESTAMP WITHOUT TIME ZONE) * INTERVAL '1 second';
As mentioned in the docs, you really need to add the epoch offset to
TIMESTAMP WITH TIME ZONE 'epoch'
----
if you want to arrive at a sane result. That would produce a globally
correct timestamp-with-TZ result, which you could cast back to timestamp
without TZ if you had a mind to.
We used to interpret EPOCH of a timestamp without TZ as if the timestamp
were in GMT, which would be a behavior that would produce the results
the OP is expecting. That was changed intentionally sometime between
7.2 and 7.3; I forget the reasoning but I'm sure we had a good reason.
Probably the easiest way to get the desired result is to use AT TIME
ZONE, ie do the extract this way:
EXTRACT(EPOCH FROM '2007-04-09 00:59:59'::TIMESTAMP
AT TIME ZONE 'GMT')
Of course this all begs the question of why the OP *isn't* using
timestamp with time zone, or at least setting his zone to GMT if
he doesn't want DST-aware calculations.
regards, tom lane
Thanks for the feedback. I would have been more suspicious of time zones if the unusual date had fallen on the 25th of march that year, this is when daylight savings starts in the timezone that postgresql uses on my system. The AT TIME ZONE sounds like the best fix for my problem. Thank you both. David. ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "David Fetter" <david@fetter.org> Cc: "David Rowley" <dgrowley@gmail.com>; <pgsql-bugs@postgresql.org> Sent: Tuesday, March 25, 2008 11:34 PM Subject: Re: [BUGS] Possible problem with EXTRACT(EPOCH FROM TIMESTAMP) > David Fetter <david@fetter.org> writes: >> On Tue, Mar 25, 2008 at 07:50:30PM -0000, David Rowley wrote: >>> 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. > > One man's bug is another man's feature ;-). The EPOCH code is designed > to produce the same result as if you had casted the timestamp to > timestamp with timezone --- the important point there being that the > stamp will be interpreted as being in your local time zone (per the > TimeZone parameter). So the problem with the OP's example is that he's > doing > > SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + EXTRACT(EPOCH FROM > '2007-04-09 > 00:59:59'::TIMESTAMP WITHOUT TIME ZONE) * INTERVAL '1 second'; > > As mentioned in the docs, you really need to add the epoch offset to > TIMESTAMP WITH TIME ZONE 'epoch' > ---- > if you want to arrive at a sane result. That would produce a globally > correct timestamp-with-TZ result, which you could cast back to timestamp > without TZ if you had a mind to. > > We used to interpret EPOCH of a timestamp without TZ as if the timestamp > were in GMT, which would be a behavior that would produce the results > the OP is expecting. That was changed intentionally sometime between > 7.2 and 7.3; I forget the reasoning but I'm sure we had a good reason. > > Probably the easiest way to get the desired result is to use AT TIME > ZONE, ie do the extract this way: > > EXTRACT(EPOCH FROM '2007-04-09 00:59:59'::TIMESTAMP > AT TIME ZONE 'GMT') > > Of course this all begs the question of why the OP *isn't* using > timestamp with time zone, or at least setting his zone to GMT if > he doesn't want DST-aware calculations. > > regards, tom lane