Date: Mon, 5 Apr 2021 13:15:22 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Bryn Llewellyn <bryn@yugabyte.com>, Zhihong Yu <zyu@yugabyte.com>,
	Isaac Morland <isaac.morland@gmail.com>,
	John W Higgins <wishdev@gmail.com>, pgsql-hackers@postgresql.org,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Have I found an interval arithmetic bug?
Message-ID: <20210405181522.GS6592@telsasoft.com>
References: <E34DA978-0C0E-4F43-97CD-278FA0C228E5@yugabyte.com>
 <20210403022126.GG29126@momjian.us>
 <20210405163310.GP6592@telsasoft.com>
 <20210405180158.GB12179@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20210405180158.GB12179@momjian.us>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210405181522.GS6592%40telsasoft.com>
Precedence: bulk

On Mon, Apr 05, 2021 at 02:01:58PM -0400, Bruce Momjian wrote:
> On Mon, Apr  5, 2021 at 11:33:10AM -0500, Justin Pryzby wrote:
> > > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> > > « …field values can have fractional parts; for example '1.5 week' or '01:02:03.45'. Such input is converted to the appropriate number of months, days, and seconds for storage. When this would result in a fractional number of months or days, the fraction is added to the lower-order fields using the conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output. »
> 
> I see that.  What is not clear here is how far we flow down.  I was
> looking at adding documentation or regression tests for that, but was
> unsure.  I adjusted the docs slightly in the attached patch.

I should have adjusted the quote to include context:

| In the verbose input format, and in SOME FIELDS of the more compact input formats, field values can have fractional parts[...]

I don't know what "some fields" means - more clarity here would help indicate
the intended behavior.

> The interaction of months/days/seconds is so imprecise that passing it
> futher down doesn't make much sense, and suggests a precision that
> doesn't exist, but if people prefer that we can do it.

I agree on its face that "months" is imprecise (30, 31, 27, 28 days),
especially fractional months, and same for "years" (leap years), and hours per
day (DST), but even minutes ("leap seconds").  But the documentation seems to
be clear about the behavior:

| .. using the conversion factors 1 month = 30 days and 1 day = 24 hours

I think the most obvious/consistent change is for years and greater to "cascade
down" to seconds, and not just months.

-- 
Justin