Re: BUG #6424: Possible error in time to seconds conversion - Mailing list pgsql-bugs

o.bousche@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.

            regards, tom lane