bruce@momjian.us wrote:
I have just posted a new version of the patch which I think covers all the right areas.
I found the relevant email from you to pgsql-hackers here:
You said:
I have modified the patch to prevent partial months from creating partial hours/minutes/seconds… Partial years keeps it in months, partial months takes it to days, and partial days take it to hours/minutes/seconds. This seems like an improvement.
I have written some PL/pgSQL code that faithfully emulates the behavior that I see in my present vanilla PostgreSQL Version 13.2 system in a wide range of tests. This is the key part:
m1 int not null := trunc(p.mo);
m_remainder numeric not null := p.mo - m1::numeric;
m int not null := trunc(p.yy*12) + m1;
d_real numeric not null := p.dd + m_remainder*30.0;
d int not null := floor(d_real);
d_remainder numeric not null := d_real - d::numeric;
s numeric not null := d_remainder*24.0*60.0*60.0 +
p.hh*60.0*60.0 +
p.mi*60.0 +
p.ss;
begin
return (m, d, s)::modeled_interval_t;
end;
These quantities:
p.yy, p.mo, p.dd, p.hh, p.mi, and p.ss
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.
I gather that by the time YugabyteDB has adopted your patch, my PL/pgSQL will no longer be a correct emulation. So I’ll re-write it then.
I intend to advise users always to constrain the values, when they specify an interval value explicitly, so the the years, months, days, hours, and minutes are integers. This is, after all, the discipline that the make_interval() built-in imposes. So I might recommend using only that.