From: Bruce Momjian <bruce@momjian.us>
To: John W Higgins <wishdev@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Have I found an interval arithmetic bug?
Date: Fri, 2 Apr 2021 19:58:15 -0400
Message-ID: <20210402235815.GC29126@momjian.us> (raw)
In-Reply-To: <CAPhAwGy9X_fqPz0ekcq0+PwUZZsitcwtuNt-qhajC5pnYtjK+g@mail.gmail.com>
References: <BDAE4B56-3337-45A2-AC8A-30593849D6C0@yugabyte.com>
<20210402180549.GF9270@momjian.us>
<CAPhAwGy9X_fqPz0ekcq0+PwUZZsitcwtuNt-qhajC5pnYtjK+g@mail.gmail.com>
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@momjian.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@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bruce@momjian.us, wishdev@gmail.com
Subject: Re: Have I found an interval arithmetic bug?
In-Reply-To: <20210402235815.GC29126@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox