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

Re: BUG #5966: extract(epoch..) function error

From: Joshua Tolley <eggyknap(at)gmail(dot)com>
To: Ricardo Solanilla <info(at)abaco-tandil(dot)com(dot)ar>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5966: extract(epoch..) function error
Date: 2011-04-20 09:45:26
Message-ID: 4daeab6d.01568f0a.16ac.41af@mx.google.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, Apr 06, 2011 at 11:17:42PM +0000, Ricardo Solanilla wrote:
> -- try this
> 
> select (extract(epoch from (date('2011-03-20'))) - extract(epoch from
> (date('2011-03-19')))) / 3600
> 
> -- it must be 24 (hours), only occurs in march 19,2011
> -- in v. 8.0 work fine!!

Are you sure this isn't just daylight saving time coming into play? I get
this, using US/Mountain time, which is explainable because DST started on
2011-03-13. The specific date where this happens will vary by your time zone
of choice, as different places start daylight saving time on different dates.

5432 josh(at)josh*# select extract(epoch from '2011-03-13'::date) - extract(epoch
from '2011-03-12'::date);
 ?column?
----------
    86400
(1 row)

5432 josh(at)josh*# select extract(epoch from '2011-03-14'::date) - extract(epoch
from '2011-03-13'::date);
 ?column?
----------
    82800
(1 row)

5432 josh(at)josh*# select extract(epoch from '2011-03-15'::date) - extract(epoch
from '2011-03-14'::date);
 ?column?
----------
    86400
(1 row)

If I set my timezone to US/Arizona, where daylight saving time is not used at
all, I get the behavior you expected:

5432 josh(at)josh*# set timezone to "US/Arizona";
SET
5432 josh(at)josh*# select extract(epoch from '2011-03-13'::date) - extract(epoch
from '2011-03-12'::date);
 ?column? 
----------
    86400
(1 row)

5432 josh(at)josh*# select extract(epoch from '2011-03-14'::date) - extract(epoch
from '2011-03-13'::date);
 ?column? 
----------
    86400
(1 row)

5432 josh(at)josh*# select extract(epoch from '2011-03-15'::date) - extract(epoch
from '2011-03-14'::date);
 ?column? 
----------
    86400
(1 row)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

In response to

pgsql-bugs by date

Next:From: Marko TiikkajaDate: 2011-04-20 12:51:50
Subject: BUG #5988: CTINE duplicates constraints
Previous:From: Heikki LinnakangasDate: 2011-04-20 07:07:51
Subject: Re: BUG #5818: initdb lose the single quote of locale

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