Re: BUG #2994: avg() calculates wrong on Interval-type

Peter Eisentraut <peter_e@gmx.net> writes:
> Moreover, my system thinks that the average of '1 day 12 hours' and '8
> hours' is 22 hours, and also that the average of '2 days' and '3 days'
> is 2 days and 12 hours, so it does make the assumption that 1 day = 24
> hours.

Yeah, that's what it does with fractional days, because it doesn't have
a lot of choice --- we could perhaps change the day field from integer
to fractional, but I don't know what 0.5 day really means, so I'm not
sure that that'd be an improvement.  What it won't do is up-convert
hours to days without being told to (via justify_hours).

The issue in its simplest form is:

regression=# select '3 days'::interval / 2;
    ?column?
----------------
 1 day 12:00:00
(1 row)

regression=# select '72 hours'::interval / 2;
 ?column?
----------
 36:00:00
(1 row)

regression=# select '3 days'::interval * 2;
 ?column?
----------
 6 days
(1 row)

regression=# select '72 hours'::interval * 2;
 ?column?
-----------
 144:00:00
(1 row)


            regards, tom lane