Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #3260: Subtracting intervals

BUG #3260: Subtracting intervals

From
"Dhugael McLean"
Date:
The following bug has been logged online:

Bug reference:      3260
Logged by:          Dhugael McLean
Email address:      box@yourtechonline.com
PostgreSQL version: 8.1.8
Operating system:   FreeBSD
Description:        Subtracting intervals
Details:

select '1 day'::interval - '55 minutes'::interval;

    ?column?
-----------------
 1 day -00:55:00

If the interval periods are both minutes (hours - hours, days - days, etc),
this works fine. Days - minutes seems to fail. This should output 23:05:00.

In case this was some weird casting error, I checked:
select interval '1 day' - interval '55 minutes';
and that failed as well.

Re: BUG #3260: Subtracting intervals

From
Tom Lane
Date:
"Dhugael McLean" <box@yourtechonline.com> writes:
> select '1 day'::interval - '55 minutes'::interval;

>     ?column?
> -----------------
>  1 day -00:55:00

> If the interval periods are both minutes (hours - hours, days - days, etc),
> this works fine. Days - minutes seems to fail. This should output 23:05:00.

No, this result is correct IMHO.  Days and minutes are not interconvertible,
because there are not always 24 hours in a day.  As an example using
EST5EDT zone (current US DST law):

regression=# select '2007-03-11'::timestamptz;
      timestamptz
------------------------
 2007-03-11 00:00:00-05
(1 row)

regression=# select '2007-03-11'::timestamptz + '1 day'::interval;
        ?column?
------------------------
 2007-03-12 00:00:00-04
(1 row)

regression=# select ('2007-03-11'::timestamptz + '1 day'::interval) - '55 minutes'::interval;
        ?column?
------------------------
 2007-03-11 23:05:00-04
(1 row)

regression=# select '2007-03-11'::timestamptz + ('1 day'::interval - '55 minutes'::interval);
        ?column?
------------------------
 2007-03-11 23:05:00-04
(1 row)

regression=# select '2007-03-11'::timestamptz + '23:05:00'::interval;
        ?column?
------------------------
 2007-03-12 00:05:00-04
(1 row)

Postgres gets the fourth case right, but would fail if we adopted
your approach, as shown by the fifth case.

            regards, tom lane

Re: BUG #3260: Subtracting intervals

From
Alvaro Herrera
Date:
Dhugael McLean wrote:

> select '1 day'::interval - '55 minutes'::interval;
>
>     ?column?
> -----------------
>  1 day -00:55:00
>
> If the interval periods are both minutes (hours - hours, days - days, etc),
> this works fine. Days - minutes seems to fail. This should output 23:05:00.

No, that answer would be wrong because not all days are 24 hours long
(think DST).  You can use justify_hours() if you want to make that
assumption:

alvherre=# select justify_hours('1 day'::interval - '55 minutes'::interval);
 justify_hours
---------------
 23:05:00
(1 fila)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.