Re: Have I found an interval arithmetic bug?

On Fri, Apr 2, 2021 at 09:02:29PM -0400, Bruce Momjian wrote:
> On Fri, Apr 2, 2021 at 05:50:59PM -0700, Bryn Llewellyn wrote:
> > are the user’s parameterization. All are real numbers. Because non-integral
> > values for years, months, days, hours, and minutes are allowed when you specify
> > a value using the ::interval typecast, my reference doc must state the rules. I
> > would have struggled to express these rules in prose—especially given the use
> > both of trunc() and floor(). I would have struggled more to explain what
> > requirements these rules meet.
>
> The fundamental issue is that while months, days, and seconds are
> consistent in their own units, when you have to cross from one unit to
> another, it is by definition imprecise, since the interval is not tied
> to a specific date, with its own days-of-the-month and leap days and
> daylight savings time changes. It feels like it is going to be
> imprecise no matter what we do.
>
> Adding to this is the fact that interval values are stored in C 'struct
> tm' defined in libc's ctime(), where months are integers, so carrying
> around non-integer month values until we get a final result would add a
> lot of complexity, and complexity to a system that is by definition
> imprecise, which doesn't seem worth it.

I went ahead and modified the interval multiplication/division functions
to use the same logic as fractional interval units:

SELECT interval '23 mons';
interval
----------------
1 year 11 mons

SELECT interval '23 mons' / 2;
?column?
-----------------
11 mons 15 days

SELECT interval '23.5 mons';
interval
------------------------
1 year 11 mons 15 days

SELECT interval '23.5 mons' / 2;
?column?
--------------------------
11 mons 22 days 12:00:00

I think the big issue is that the casting to interval into integer
mons/days/secs so we can no longer make the distinction of units >
months vs months.

Using Bryn's example, the master branch output is:

SELECT
interval '1.3443 years' as i1,
interval '1 years' * 1.3443 as i2;
i1 | i2
---------------+---------------------------------
1 year 4 mons | 1 year 4 mons 3 days 22:45:07.2

and the attached patch output is:

SELECT
interval '1.3443 years' as i1,
interval '1 years' * 1.3443 as i2;
i1 | i2
---------------+----------------------
1 year 4 mons | 1 year 4 mons 4 days

which looks like an improvement.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.