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

On Wed, Feb 1, 2012 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> o.bousche@krohne.com writes:
>> Should the query
>
>> select
>> =A0 extract(epoch
>> =A0 =A0 from cast('2012-01-01 14:30:1' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0timestamp) -
>> =A0 =A0 =A0 =A0 =A0cast('1970-01-01 0:0:0' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 timestamp))) -
>> =A0 extract(epoch
>> =A0 =A0 from (cast('2012-01-01 14:30:1' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 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=3D# 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=3D# 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