Re: BUG #1993: Adding/subtracting negative time intervals - Mailing list pgsql-bugs

From Michael Fuhr
Subject Re: BUG #1993: Adding/subtracting negative time intervals
Date
Msg-id 20051025033826.GA33218@winnie.fuhr.org
Whole thread Raw
In response to Re: BUG #1993: Adding/subtracting negative time intervals  (Klint Gore <kg@kgb.une.edu.au>)
List pgsql-bugs
On Tue, Oct 25, 2005 at 12:48:10PM +1000, Klint Gore wrote:
> On Tue, 25 Oct 2005 08:51:59 +1000, Russell Smith <mr-russ@pws.com.au> wrote:
> > Looks to mee like Daylight Savings has conveniently started.
>
> But the elapsed time for those results is only 6 days, 23 hours.
>
> That's changed since v7.4.7

I think this item in the 8.1 Release Notes might be relevant:

* Add an internal day field to INTERVAL so a one day interval can be
  distinguished from a 24 hour interval (Michael Glaesemann)

  Days that contain a daylight savings time adjustment are not 24 hours,
  but typically 23 or 25 hours.  This change allows days (not fixed
  24-hour periods) to be added to dates who's result includes a daylight
  savings time adjustment period.  Therefore, while in previous releases
  1 day and 24 hours were interchangeable interval values, in this
  release they are treated differently, e.g.

    '2005-05-03 00:00:00 EST' + '1 day' = '2005-05-04 00:00:00-04'
    '2005-05-03 00:00:00 EST' + '24 hours' = '2005-05-04 01:00:00-04'

Here's an example and the results from 7.4.9, 8.0.4, and 8.1beta4:

\x
SET TimeZone TO 'Australia/NSW';
SELECT version(), now(), now() + interval'1 week', now() + interval'168 hours';

-[ RECORD 1 ]-----------------------------------------------------------------------
version  | PostgreSQL 7.4.9 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now      | 2005-10-25 13:35:43.663169+10
?column? | 2005-11-01 14:35:43.663169+11
?column? | 2005-11-01 14:35:43.663169+11

-[ RECORD 1 ]-----------------------------------------------------------------------
version  | PostgreSQL 8.0.4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now      | 2005-10-25 13:35:45.459081+10
?column? | 2005-11-01 14:35:45.459081+11
?column? | 2005-11-01 14:35:45.459081+11

-[ RECORD 1 ]--------------------------------------------------------------------------
version  | PostgreSQL 8.1beta4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
now      | 2005-10-25 13:35:47.104595+10
?column? | 2005-11-01 13:35:47.104595+11
?column? | 2005-11-01 14:35:47.104595+11

--
Michael Fuhr

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Next
From: Michael Fuhr
Date:
Subject: Re: BUG #1993: Adding/subtracting negative time intervals