Re: BUG #1993: Adding/subtracting negative time intervals

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > Should 24 hours be the same as 1 * 24 hours?
>
> Yes, I would think so.
>
> > The latter appears to be equal to 1 day, not 24 hours:
>
> Urgh. I think this is a serious thinko in Michael Glaesemann's rewrite
> of interval_mul. The application of interval_justify_hours is utterly
> wrong ... and in fact, I'm not sure it should be applied in any of the
> three functions that currently call it. I don't mind the user deciding
> he'd like to flatten '24 hours' to '1 day' but the basic arithmetic
> functions for intervals have no business doing that.

The reason interval_justify_hours is called by interval multiplication
is so multipling an interval '2 days, 4 hours' by 10 doesn't return
values like 20 days, 40 hours, etc, but instead something like '21 days,
16 hours', which seems more reasonable.

For a query like:

test=> SELECT '2005-10-29 12:00:00-06'::timestamptz + 1 * '24 hours'::interval;

the interval multiplication really has no fixed timestamp associated
with it, so it seems good to adjust the output. That result is _then_
added to an interval, and this is where the problem happens, where this
computes to 1 day:

test=> select 1 * '24 hours'::interval;
?column?
----------
1 day
(1 row)

I would say if intervals are going to be added to timestamps, we
probably don't want the adjustment, but if they are going to be used on
their own, it seems the adjustment makes sense. One solution would be
to suggest the use of interval_justify_hours() in the documentation for
interval multiplication, and prevent the justification from happening
automatically.

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