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.241604months 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
correspondinglyyet more quirky. The rules can be written down. But they’re too tortuos to allow an ordinary mortal
confidentlyto 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
feeblesearching 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
applicationswill 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 <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
