On Fri, Apr 2, 2021 at 09:02:29PM -0400, Bruce Momjian wrote: > On Fri, Apr 2, 2021 at 05:50:59PM -0700, Bryn Llewellyn wrote: > > are the user’s parameterization. All are real numbers. Because non-integral > > values for years, months, days, hours, and minutes are allowed when you > > specify > > a value using the ::interval typecast, my reference doc must state the > > rules. I > > would have struggled to express these rules in prose—especially given the > > use > > both of trunc() and floor(). I would have struggled more to explain what > > requirements these rules meet. > > The fundamental issue is that while months, days, and seconds are > consistent in their own units, when you have to cross from one unit to > another, it is by definition imprecise, since the interval is not tied > to a specific date, with its own days-of-the-month and leap days and > daylight savings time changes. It feels like it is going to be > imprecise no matter what we do. > > Adding to this is the fact that interval values are stored in C 'struct > tm' defined in libc's ctime(), where months are integers, so carrying > around non-integer month values until we get a final result would add a > lot of complexity, and complexity to a system that is by definition > imprecise, which doesn't seem worth it.
I went ahead and modified the interval multiplication/division functions
to use the same logic as fractional interval units:
SELECT interval '23 mons';
interval
----------------
1 year 11 mons
SELECT interval '23 mons' / 2;
?column?
-----------------
11 mons 15 days
SELECT interval '23.5 mons';
interval
------------------------
1 year 11 mons 15 days
SELECT interval '23.5 mons' / 2;
?column?
--------------------------
11 mons 22 days 12:00:00
I think the big issue is that the casting to interval into integer
mons/days/secs so we can no longer make the distinction of units >
months vs months.
Using Bryn's example, the master branch output is:
SELECT
interval '1.3443 years' as i1,
interval '1 years' * 1.3443 as i2;
i1 | i2
---------------+---------------------------------
1 year 4 mons | 1 year 4 mons 3 days 22:45:07.2
and the attached patch output is:
SELECT
interval '1.3443 years' as i1,
interval '1 years' * 1.3443 as i2;
i1 | i2
---------------+----------------------
1 year 4 mons | 1 year 4 mons 4 days
which looks like an improvement.
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
interval.diff.gz
Description: application/gzip
