Have I found an interval arithmetic bug?

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Have I found an interval arithmetic bug?
Date: 2021-04-02 04:46:58
Message-ID: BDAE4B56-3337-45A2-AC8A-30593849D6C0@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

As I reason it, the last four “select” statements are all semantically the same. They’re just different syntaxes to add the two intervals the the first two “select” statements use separately. There’s one odd man out. And I reason this one to be wrong. Is there a flaw in my reasoning?

Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4 months. But the fractional part of the months value doesn’t spread further down into days. However, the fractional part of “29.4 months” (12 days) _does_ spread further down into days. What’s the rationale for this asymmetry?

I can’t see that my observations here can be explained by the difference between calendar time and clock time. Here I’m just working with non-metric units like feet and inches. One year is just defined as 12 months. And one month is just defined as 30 days. All that stuff about adding a month to 3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) , and that other stuff about adding one day to 23:00 on the day before the “spring forward” moment taking you to 23:00 on the next day (i.w. when intervals are added to timestamps) is downstream of simply adding two intervals.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hemil Ruparel 2021-04-02 06:56:54 Re: Primary keys and composite unique keys(basic question)
Previous Message Rob Sargent 2021-04-02 03:26:30 Re: Primary keys and composite unique keys(basic question)

Browse pgsql-hackers by date

  From Date Subject
Next Message tsunakawa.takay@fujitsu.com 2021-04-02 05:18:40 RE: Add client connection check during the execution of the query
Previous Message Michael Paquier 2021-04-02 04:45:31 Re: Proposal: Save user's original authenticated identity for logging