On Mon, Apr 12, 2021 at 4:22 PM Bruce Momjian <bruce@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