Re: BUG #5966: extract(epoch..) function error - Mailing list pgsql-bugs

From Joshua Tolley
Subject Re: BUG #5966: extract(epoch..) function error
Date
Msg-id 4daeab6d.01568f0a.16ac.41af@mx.google.com
Whole thread Raw
In response to BUG #5966: extract(epoch..) function error  ("Ricardo Solanilla" <info@abaco-tandil.com.ar>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: BUG #5818: initdb lose the single quote of locale
Next
From: "Marko Tiikkaja"
Date:
Subject: BUG #5988: CTINE duplicates constraints