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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: o(dot)bousche(at)krohne(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6424: Possible error in time to seconds conversion
Date: 2012-02-01 16:00:25
Message-ID: 15144.1328112025@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2012-02-01 16:04:15 Re: BUG #6425: Bus error in slot_deform_tuple
Previous Message Robert Haas 2012-02-01 15:38:40 Re: BUG #6200: standby bad memory allocations on SELECT