Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers

> On 21-Jul-2021, at 01:23, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Wed, 21 Jul 2021 at 03:48, Bruce Momjian <bruce@momjian.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
fora 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