From: | John W Higgins <wishdev(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Have I found an interval arithmetic bug? |
Date: | 2021-04-02 21:00:03 |
Message-ID: | CAPhAwGy9X_fqPz0ekcq0+PwUZZsitcwtuNt-qhajC5pnYtjK+g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Fri, Apr 2, 2021 at 11:05 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
> > Or am I misunderstanding something?
> >
> > Try this. The result of each “select” is shown as the trailing comment
> on the
> > same line. I added whitespace by hand to line up the fields.
> >
> > select interval '-1.7 years'; -- -1 years -8
> mons
> >
> > select interval '29.4 months'; -- 2 years 5
> mons 12
> > days
> >
> > select interval '-1.7 years 29.4 months'; -- 8
> mons 12
> > days << wrong
> > select interval '29.4 months -1.7 years'; -- 9
> mons 12
> > days
> >
> > select interval '-1.7 years' + interval '29.4 months'; -- 9
> mons 12
> > days
> > select interval '29.4 months' + interval '-1.7 years'; -- 9
> mons 12
> > days
> >
>
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.
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.
John
From | Date | Subject | |
---|---|---|---|
Next Message | Allie Crawford | 2021-04-02 22:01:34 | How to install PostgreSQL binaries on a different directory than the default one |
Previous Message | Zhihong Yu | 2021-04-02 20:27:33 | Re: Have I found an interval arithmetic bug? |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2021-04-02 21:08:46 | Re: Bug? pg_identify_object_as_address() et al doesn't work with pg_enum.oid |
Previous Message | Chapman Flack | 2021-04-02 20:59:22 | Re: [PATCH] Implement motd for PostgreSQL |