From: Bruce Momjian <bruce@momjian.us>
To: Bryn Llewellyn <bryn@yugabyte.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Have I found an interval arithmetic bug?
Date: Fri, 2 Apr 2021 20:36:27 -0400
Message-ID: <20210403003627.GD29126@momjian.us> (raw)
In-Reply-To: <20210402234732.GA29125@momjian.us>
References: <BDAE4B56-3337-45A2-AC8A-30593849D6C0@yugabyte.com>
<20210402180549.GF9270@momjian.us>
<E34DA978-0C0E-4F43-97CD-278FA0C228E5@yugabyte.com>
<20210402234732.GA29125@momjian.us>
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@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, bryn@yugabyte.com
Subject: Re: Have I found an interval arithmetic bug?
In-Reply-To: <20210403003627.GD29126@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