Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group