On Wed, Feb 1, 2012 at 10:00 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> o.bous...@krohne.com writes:
>> Should the query
>
>> select
>> extract(epoch
>> from cast('2012-01-01 14:30:1' as
>> timestamp) -
>> cast('1970-01-01 0:0:0' as
>> timestamp))) -
>> extract(epoch
>> from (cast('2012-01-01 14:30:1' as
>> timestamp)))
>
>> return 0 instead of 3600?
>
> Well, right now it's operating as designed, because extract(epoch,
> timestamp without timezone) tries to rotate the timestamp from local
> time to GMT so that "epoch 0" corresponds to midnight GMT 1970-01-01.
> (I presume that you are in a GMT+1 timezone.)
>
> Changing that behavior is one of the possible solutions to the problem
> being discussed over here:
> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
> but I don't believe we have any consensus yet about whether that
> would be a good idea.
TBH, I think the behavior of the example given is 100% correct *if a
timezone isn't specified', which the OP didn't. It's only weird if
you do this:
postgres=# select extract(epoch from '2012-01-01 14:30:1'::timestamp -
'1970-01-01 0:0:0 GMT'::timestamp)
- extract(epoch from '2012-01-01 14:30:1'::timestamp);
which really boils down to this:
postgres=# select extract(epoch from '1970-01-01 0:0:0 GMT'::timestamp);
date_part
-----------
21600
(1 row)
which is what seems busted to me.
merlin
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
