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.
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) |
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 |