Re: Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "David Fetter" <david(at)fetter(dot)org>
Cc: "David Rowley" <dgrowley(at)gmail(dot)com>; <pgsql-bugs(at)postgresql(dot)org>
Sent: Tuesday, March 25, 2008 11:34 PM
Subject: Re: [BUGS] Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)

> David Fetter <david(at)fetter(dot)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