Re: Have I found an interval arithmetic bug?

On Fri, Apr 2, 2021 at 07:47:32PM -0400, Bruce Momjian wrote:
> I have modified the patch to prevent partial months from creating
> partial hours/minutes/seconds, so the output is now at least consistent
> based on the three units:
>
> SELECT ('6.54321 years'::interval)::text as i;
> i
> ----------------
> 6 years 7 mons
>
> SELECT ('6.54321 months'::interval)::text as i;
> i
> ----------------
> 6 mons 16 days
>
> SELECT ('876.54321 days'::interval)::text as i;
> i
> -----------------------
> 876 days 13:02:13.344
>
> Partial years keeps it in months, partial months takes it to days, and
> partial days take it to hours/minutes/seconds. This seems like an
> improvement.
>
> This also changes the regression test output, I think for the better:
>
> SELECT INTERVAL '1.5 weeks';
> i
> ------------------
> - 10 days 12:00:00
> + 10 days
>
> The new output is less precise, but probably closer to what the user
> wanted.

Thinking some more about this, the connection between months and days is
very inaccurate, 30 days/month, but the connection between days and
hours/minutes/seconds is pretty accurate, except for leap days.
Therefore, returning "10 days 12:00:00" is in many ways better, but
returning hours/minutes/seconds for fractional months is very arbitrary
and suggests an accuracy that doesn't exist. However, I am afraid that
trying to enforce that distinction in the Postgres behavior would appear
very arbitrary, so what I did above is proabably the best I can do.
Here is another example of what we have:

SELECT INTERVAL '1.5 years';
interval
---------------
1 year 6 mons

SELECT INTERVAL '1.5 months';
interval
---------------
1 mon 15 days

SELECT INTERVAL '1.5 weeks';
interval
----------
10 days

SELECT INTERVAL '1.5 days';
interval
----------------
1 day 12:00:00

SELECT INTERVAL '1.5 hours';
interval
----------
01:30:00

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