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

The following bug has been logged online:

Bug reference: 5966
Logged by: Ricardo Solanilla
Email address: info(at)abaco-tandil(dot)com(dot)ar
PostgreSQL version: 8.3.0 b.1400
Operating system: windows nt
Description: extract(epoch..) function error
Details:

-- 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!!

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