Re: BUG #6424: Possible error in time to seconds conversion

On Wed, Feb 1, 2012 at 10:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> o(dot)bousche(at)krohne(dot)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