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

Klint Gore wrote:
> On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
> >         ?column?
> > ------------------------
> >  2005-10-30 13:22:00-05
> > (1 row)
> >
> > regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz;
> >     ?column?
> > ----------------
> >  1 day 01:00:00
> > (1 row)
> >
> > ISTM that given the former result, the latter calculation ought to
> > produce '1 day', not something else.
>
> Would the '1 day' result know it was 24 hours or be the new 23/24/25
> hour version of '1 day'?

It has no idea.  When you do a subtraction, it isn't clear if you are
interested in "days" or "hours", so we give hours.  If you want days,
you should convert the timestamps to dates and just subtract them.

> If it was the new version, could you get the original values back?
> i.e. what would be the result of
> select
> ('2005-10-29 13:22:00-04'::timestamptz +
> ('2005-10-30 13:22:00-05'::timestamptz -
>  '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';

You bring up a good point here.  With current CVS your subtraction
yields:

    test-> ('2005-10-30 13:22:00-05'::timestamptz -
    test(>  '2005-10-29 13:22:00-04'::timestamptz);
        ?column?
    ----------------
     1 day 01:00:00
    (1 row)

so adding that to the first timestamp gets:

    test=> select
    test-> ('2005-10-29 13:22:00-04'::timestamptz +
    test(> ('2005-10-30 13:22:00-05'::timestamptz -
    test(>  '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
          timezone
    ---------------------
     2005-10-30 14:22:00
    (1 row)

This is certainly _not_ what someone would expect as a return value.
What happens is that we subtract to generate the number of hours
different, but then get all smart that "oh, that is one day to add, and
one hour" and return an unexpected value.

This is actually a good argument that the use of
interval_justify_hours() in timestamp_mi() is a mistake.  Without this
call, we have:

    test=> select
    test-> ('2005-10-30 13:22:00-05'::timestamptz -
    test(>  '2005-10-29 13:22:00-04'::timestamptz);
     ?column?
    ----------
     25:00:00
    (1 row)

and

    test=> select
    test-> ('2005-10-29 13:22:00-04'::timestamptz +
    test(> ('2005-10-30 13:22:00-05'::timestamptz -
    test(>  '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
          timezone
    ---------------------
     2005-10-30 13:22:00
    (1 row)

but it also has the tendency to return some very high values for hours:

    test=> select
    test-> ('2005-12-30 13:22:00-05'::timestamptz -
    test(>  '2005-10-29 13:22:00-04'::timestamptz);
      ?column?
    ------------
     1489:00:00
    (1 row)

but again, if you want days, you can cast to days.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073