Re: 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