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

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

From
"Ricardo Solanilla"
Date:
The following bug has been logged online:

Bug reference:      5966
Logged by:          Ricardo Solanilla
Email address:      info@abaco-tandil.com.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!!

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

From
Joshua Tolley
Date:
On Wed, Apr 06, 2011 at 11:17:42PM +0000, Ricardo Solanilla wrote:
> -- try this
>=20
> select (extract(epoch from (date('2011-03-20'))) - extract(epoch from
> (date('2011-03-19')))) / 3600
>=20
> -- 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 date=
s.

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

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

5432 josh@josh*# select extract(epoch from '2011-03-15'::date) - extract(ep=
och
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@josh*# set timezone to "US/Arizona";
SET
5432 josh@josh*# select extract(epoch from '2011-03-13'::date) - extract(ep=
och
from '2011-03-12'::date);
 ?column?=20
----------
    86400
(1 row)

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

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

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