Re: Have I found an interval arithmetic bug?

Hi,
bq. My new code returns 0.2 months for this, not zero

Can you clarify (the output below that was 2 mons, not 0.2) ?

Thanks

On Fri, Apr 2, 2021 at 4:58 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, Apr 2, 2021 at 02:00:03PM -0700, John W Higgins wrote:
> > On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > While maybe there is an argument to fixing the negative/positive
> rounding issue
> > - there is no way this gets solved without breaking the current
> implementation
> >
> > select interval '0.3 years' + interval '0.4 years' - interval '0.7
> years' +
> > interval '0.1 years' should not equal 0 but it certainly does.
>
> My new code returns 0.2 months for this, not zero:
>
> SELECT interval '0.3 years' + interval '0.4 years' -
> interval '0.7 years' + interval '0.1 years';
> ?column?
> ----------
> 2 mons
>
> which is also wrong since:
>
> SELECT interval '0.1 years';
> interval
> ----------
> 1 mon
>
> > Unless we take the concept of 0.3 years = 3 months and move to something
> along
> > the lines of
> >
> > 1 year = 360 days
> > 1 month = 30 days
> >
> > so therefore
> >
> > 0.3 years = 360 days * 0.3 = 108 days = 3 months 18 days
> > 0.4 years = 360 days * 0.4 = 144 days = 4 months 24 days
> > 0.7 years = 360 days * 0.7 = 252 days = 8 months 12 days
> >
> > Then, and only if we don't go to any more than tenths of a year, does
> the math
> > work. Probably this should resolve down to seconds and then work
> backwards -
> > but unless we're looking at breaking the entire way it currently resolves
> > things - I don't think this is of much value.
> >
> > Doing math on intervals is like doing math on rounded numbers - there is
> always
> > going to be a pile of issues because the level of precision just is not
> good
> > enough.
>
> I think the big question is what units do people want with fractional
> values. I have posted a follow-up email that spills only for one unit,
> which I think is the best approach.
>
> --
> 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.
>
>
>
>