Re: Have I found an interval arithmetic bug?

> On 21-Jul-2021, at 01:23, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> On Wed, 21 Jul 2021 at 03:48, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>> this example now gives me concern:
>>
>> SELECT INTERVAL '1.06 months 1 hour';
>> interval
>> -----------------------
>> 1 mon 2 days 01:00:00
>>
>> Notice that it rounds the '1.06 months' to '1 mon 2 days', rather than
>> spilling to hours/minutes/seconds, even though hours is already
>> specified. I don't see a better way to handle this than the current
>> code already does, but it is something odd.
>
> Hmm, looking at this whole thread, I have to say that I prefer the old
> behaviour of spilling down to lower units.
>
> For example, with this patch:
>
> SELECT '0.5 weeks'::interval;
> interval
> ----------
> 4 days
>
> which I don't think is really an improvement. My expectation is that
> half a week is 3.5 days, and I prefer what it used to return, namely
> '3 days 12:00:00'.
>
> It's true that that leads to odd-looking results when the field value
> has lots of fractional digits, but that was at least explainable, and
> followed the documentation.
>
> Looking for a general principle to follow, how about this -- the
> result of specifying a fractional value should be the same as
> multiplying an interval of 1 unit by that value. In other words,
> '1.8594 months'::interval should be the same as '1 month'::interval *
> 1.8594. (Actually, it probably can't easily be made exactly the same
> in all cases, due to differences in the floating point computations in
> the two cases, and rounding errors, but it's hopefully not far off,
> unlike the results obtained by not spilling down to lower units on
> input.)
>
> The cases that are broken in master, in my opinion, are the larger
> units (year and above), which don't propagate down in the same way as
> fractional months and below. So, for example, '0.7 years' should be
> 8.4 months (with the conversion factor of 1 year = 12 months), giving
> '8 months 12 days', which is what '1 year'::interval * 0.7 produces.
> Sure, there are arguably more accurate ways of computing that.
> However, that's the result obtained using the documented conversion
> factors, so it's justifiable in those terms.
>
> It's worth noting another case that is broken in master:
>
> SELECT '1.7 decades'::interval;
> interval
> ------------------
> 16 years 11 mons
>
> which is surely not what anyone would expect. The current patch fixes
> this, but it would also be fixed by handling the fractional digits for
> these units in the same way as for smaller units. There was an earlier
> patch doing that, I think, though I didn't test it.
>
> Regards,
> Dean

And try these two tests. (I’m using Version 13.3.) on current MacOS.

select
'1.7 decades'::interval as i1,
('1 decades'::interval)*1.7 as i2,
('10 years'::interval)*1.7 as i3;

i1 | i2 | i3
------------------+----------+----------
16 years 11 mons | 17 years | 17 years

select
'1.7345 decades'::interval as i4,
('1 decades'::interval)*1.7345 as i5,
('10 years'::interval)*1.7345 as i6;

i4 | i5 | i6
-----------------+---------------------------------+---------------------------------
17 years 4 mons | 17 years 4 mons 4 days 04:48:00 | 17 years 4 mons 4 days 04:48:00

Shows only what we know already: mixed interval arithmetic is fishy.

Seems to me that units like “weeks”, “centuries”, “millennia”, and so on are a solution (broken in some cases) looking for a problem. Try this (and variants like I showed above):

select
'1.7345 millennia'::interval as i7,
'1.7345 centuries'::interval as i8,
'1.7345 weeks'::interval as i9;

i7 | i8 | i9
-------------------+------------------+--------------------
1734 years 6 mons | 173 years 5 mons | 12 days 03:23:45.6