On Mon, Apr 12, 2021 at 4:22 PM Bruce Momjian <br...@momjian.us> wrote:
> On Mon, Apr 12, 2021 at 03:09:48PM -0700, Bryn Llewellyn wrote: > > I showed you all this example a long time ago: > > > > select ( > > ' > > 3.853467 years > > '::interval > > )::text as i; > > > > This behavior is the same in the env. of Bruce’s patch as in unpatched > PG 13.2. This is the result. > > > > 3 years 10 mons > > > > Notice that "3.853467 years" is "3 years" plus "10.241604 months". This > explains the "10 mons" in the result. But the 0.241604 months remainder did > not spill down into days. > > > > Can anybody defend this quirk? An extension of this example with a real > number of month in the user input is correspondingly yet more quirky. The > rules can be written down. But they’re too tortuos to allow an ordinary > mortal confidently to design code that relies on them. > > > > (I was unable to find any rule statement that lets the user predict this > in the doc. But maybe that’s because of my feeble searching skills.) > > > > If there is no defense (and I cannot imagine one) might Bruce’s patch > normalize this too to follow this rule: > > > > — convert 'y years m months' to the real number y*12 + m. > > > > — record truc( y*12 + m) in the "months" field of the internal > representation > > > > — flow the remainder down to days (but no further) > > > > After all, you've bitten the bullet now and changed the behavior. This > means that the semantics of some extant applications will change. So... in > for a penny, in for a pound? > > The docs now say: > > Field values can have fractional parts; for example, <literal>'1.5 > weeks'</literal> or <literal>'01:02:03.45'</literal>. The fractional > --> parts are used to compute appropriate values for the next lower-order > internal fields (months, days, seconds). > > meaning fractional years flows to the next lower internal unit, months, > and no further. Fractional months would flow to days. The idea of not > flowing past the next lower-order internal field is that the > approximations between units are not precise enough to flow accurately. > > With my patch, the output is now: > > SELECT INTERVAL '3 years 10.241604 months'; > interval > ------------------------ > 3 years 10 mons 7 days > > It used to flow to seconds. > > -- > Bruce Momjian <br...@momjian.us> https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > > Based on the results of more samples, I restore +1 to Bruce's latest patch. Cheers
