Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers

On Fri, Jul 30, 2021 at 12:49:34PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Now that I think of it, I will just remove the word "rounded" from the
> > back branch docs so we are technically breaking the documented API less
> > in PG 15.
> 
> I think your first idea was better.  Not documenting the behavior
> doesn't make this not an API change; it just makes it harder for
> people to understand what changed.

OK.  However, I thought we were more worried about changing documented
APIs than undocumented ones.  Anyway, I will do as you suggested.

> The doc patch itself is not exactly fine:
> 
> +     Field values can have fractional parts;  for example, <literal>'1.5
> +     weeks'</literal> or <literal>'01:02:03.45'</literal>.  However,
> 
> I think "some field values", as it was worded previously, was better.
> If you try to write 01.5:02:03, that is not going to be interpreted
> as 1.5 hours.  (Hmm, I get something that seems quite insane:
> 
> regression=# select '01.5:02:03'::interval;
>     interval    
> ----------------
>  1 day 14:03:00
> (1 row)
> 
> I wonder what it thinks it's doing there.)

It thinks 01.5:02:03 is Days:Hours;Minute, so I think all fields can use
fractions:

    SELECT interval '1.5 minutes';
     interval
    ----------
     00:01:30

> This is wrong:
> 
> +     because interval internally stores only three integer units (months,
> +     days, seconds), fractional units must be spilled to smaller units.
> 
> s/seconds/microseconds/ is probably enough to fix that.

OK, there were a few place that said "seconds" so I fixed those too.

> +     For example, because months are approximated to equal 30 days,
> +     fractional values of units greater than months is rounded to be the
> +     nearest integer number of months.  Fractional units of months or less
> +     are computed to be an integer number of days and seconds, assuming
> +     24 hours per day.  For example, <literal>'1.5 months'</literal>
> +     becomes <literal>1 month 15 days</literal>.
> 
> This entire passage is vague, and grammatically shaky too.  Perhaps
> more like
> 
>       Fractional parts of units larger than months are rounded to the
>       nearest integer number of months; for example '1.5 years'
>       becomes '1 year 6 mons'.  Fractional parts of months are rounded
>       to the nearest integer number of days, using the assumption that
>       one month equals 30 days; for example '1.5 months'

The newest patch actually doesn't work as explained above --- fractional
months now continue to spill to microseconds.  I think you are looking
at a previous version.

>       becomes '1 mon 15 days'.  Fractional parts of days and weeks
>       are converted to microseconds, using the assumption that one day
>       equals 24 hours.

Uh, fractional weeks can be integer days.

>       On output, the months field is shown as an appropriate number of
>       years and months; the days field is shown as-is; the microseconds
>       field is converted to hours, minutes, and possibly-fractional
>       seconds.

Here is an updated patch that includes some of your ideas.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.