Date: Fri, 2 Apr 2021 20:36:27 -0400
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?
Message-ID: <20210403003627.GD29126@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20210402234732.GA29125@momjian.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210403003627.GD29126%40momjian.us>
Precedence: bulk

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.