Thread: Subtracting Two Intervals

In porting from 7.4.19 to 8.3.1 I have found the following:

7.4.19:
mwr=# select interval '1 day 15 hours 30 minutes' - interval '29 hours';
      ?column?
--------------------
 @ 10 hours 30 mins
(1 row)

8.3.1:
mwr83=# select interval '1 day 15 hours 30 minutes' - interval '29 hours';
          ?column?
----------------------------
 @ 1 day -13 hours -30 mins
(1 row)

Is that right? I mean if you take 1 day (24 hours) and add -13 hours and -30
minutes, you get 10 hours and 30 minutes, but is it supposed to display that
way?

TIA
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

Terry Lee Tucker wrote:
> In porting from 7.4.19 to 8.3.1 I have found the following:
>
> 7.4.19:
> mwr=# select interval '1 day 15 hours 30 minutes' - interval '29 hours';
>       ?column?
> --------------------
>  @ 10 hours 30 mins
> (1 row)
>
> 8.3.1:
> mwr83=# select interval '1 day 15 hours 30 minutes' - interval '29 hours';
>           ?column?
> ----------------------------
>  @ 1 day -13 hours -30 mins
> (1 row)
>
> Is that right? I mean if you take 1 day (24 hours) and add -13 hours and -30
> minutes, you get 10 hours and 30 minutes, but is it supposed to display that
> way?

We removed assumptions that every day is 24 hours between those
releases. You can use justify_hours() to get the proper result:

    test=> select justify_hours(interval '1 day 15 hours 30 minutes' -
    interval '29 hours');
     justify_hours
    ---------------
     10:30:00
    (1 row)

There have been good arguments that justify_hours() behavior should be
the default.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +