Re: Have I found an interval arithmetic bug?

On Wed, Jul 28, 2021 at 12:42 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:

> On Wed, 28 Jul 2021 at 00:08, John W Higgins <wishdev(at)gmail(dot)com> wrote:
> >
> > It's nice to envision all forms of fancy calculations. But the fact is
> that
> >
> > '1.5 month'::interval * 2 != '3 month"::interval
> >
>
> That's not exactly true. Even without the patch:
>
> SELECT '1.5 month'::interval * 2 AS product,
> '3 month'::interval AS expected,
> justify_interval('1.5 month'::interval * 2) AS justified_product,
> '1.5 month'::interval * 2 = '3 month'::interval AS equal;
>
> product | expected | justified_product | equal
> ----------------+----------+-------------------+-------
> 2 mons 30 days | 3 mons | 3 mons | t
> (1 row)
>
>
That's viewing something via the mechanism that is incorrectly (technically
speaking) doing the work in the first place. It believes they are the same
- but they are clearly not when actually used.

select '1/1/2001'::date + (interval '3 month');
?column?
---------------------
2001-04-01 00:00:00
(1 row)

vs

select '1/1/2001'::date + (interval '1.5 month' * 2);
?column?
---------------------
2001-03-31 00:00:00
(1 row)

That's the flaw in this entire body of work - we keep taking fractional
amounts - doing round offs and then trying to add or multiply the pieces
back and ending up with weird floating point math style errors. That's
never to complain about it - but we shouldn't be looking at edge cases with
things like 1 month * 1.234 when 1.5 months * 2 doesn't work properly.

John

P.S. Finally we have items like this

select '12/1/2001'::date + (interval '1.5 months' * 2);
?column?
---------------------
2002-03-03 00:00:00
(1 row)

postgres=# select '1/1/2001'::date + (interval '1.5 months' * 2);
?column?
---------------------
2001-03-31 00:00:00
(1 row)

Which only has a 28 day gap because of the length of February - clearly
this is not working quite right.