Skip site navigation (1) Skip section navigation (2)

Re: Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)

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
Subject: Re: Possible problem with EXTRACT(EPOCH FROM TIMESTAMP)
Date: 2008-03-25 23:34:52
Message-ID: 2628.1206488092@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-03-26 00:11:06
Subject: Re: BUG #4057: SUM returns NULL when given no rows
Previous:From: Bruce NairnDate: 2008-03-25 22:32:09
Subject: BUG #4060: libpq - large stack causes TCP/IP error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group