Re: Have I found an interval arithmetic bug?

> On 05-Apr-2021, at 13:35, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Mon, Apr 5, 2021 at 01:06:36PM -0700, Bryn Llewellyn wrote:
>>> On 05-Apr-2021, at 11:37, Bruce Momjian <bruce(at)momjian(dot)us> wrote On:
>>> Mon, Apr 5, 2021 at 01:15:22PM -0500, Justin Pryzby wrote :
>>
>> It seems to me that this whole business is an irrevocable mess. The
>> original design could have brought three overload-distinguishable
>> types, "interval month", "interval day", and "interval second"—each
>> represented internally as a scalar. There could have been built-ins
>> to convert between them using conventionally specified rules. Then
>> interval arithmetic would have been clear. For example, an attempt to
>> assign the difference between two timestamps to anything but "interval
>> second" would cause an error (as it does in Oracle database, even
>> though there there are only two interval kinds). But we can only deal
>> with what we have and accept the fact that the doc will inevitably be
>> tortuous.
>
> The problem with making three data types is that someone is going to
> want to use a mixture of those, so I am not sure it is a win.
>
>> Givea this, I agree that fractional years should simply convert to
>> fractional months (to be then added to verbetim-given fractional
>> months) just before representing the months as the trunc() of the
>> value and cascading the remainder down to days. Units like century
>> would fall out naturally in the same way.
>
> I am confused --- are you saying we should do the interval addition,
> then truncate, because we don't do that now, and it would be hard to do.
>
>> ABOUT LEAP SECONDS
>>
>> Look at this (from Feb 2005):
>>
>> « PostgreSQL does not support leap seconds
>> https://www.google.com/url?q=https://www.postgresql.org/message-id/1162319515.20050202141132@mail.r&source=gmail-imap&ust=1618259739000000&usg=AOvVaw0lT0Zz_HDsCrF5HrWCjplE
>> u »
>>
>> I don't know if the title reports a state of affairs in the hope that
>> this be changed to bring such support—or whether it simply states
>> what obtains and always will. Anyway, a simple test (below) shows that
>> PG Version 13.2 doesn't honor leap seconds.
>
> Postgres is documented as not supporting leap seconds:
>
> https://www.google.com/url?q=https://www.postgresql.org/docs/13/functions-datetime.html%23FUNCTIONS-DATETIME-EXTRACT&source=gmail-imap&ust=1618259739000000&usg=AOvVaw35xJBdHRIsAYVV4pTzs0wR
>
> timezone
>
> The time zone offset from UTC, measured in seconds. Positive values
> correspond to time zones east of UTC, negative values to zones west of
> UTC. (Technically, PostgreSQL does not use UTC because leap seconds are
> not handled.)

Thanks for the “leap seconds not supported” link. Google’s search within site refused to find that for me. (Talk about well hidden).

About “ three data [interval] types” it’s too late anyway. So I’ll say no more.

Re “are you saying we should do the interval addition, then truncate, because we don't do that now, and it would be hard to do.” I wan’t thinking of interval addition at all. Simply how the three values that that make up the internal representation are computed from a specified interval value. Like the PL/pgSQL simulation I showed you in an earlier reply. I can't find that in the archive now. So here it is again. Sorry for the repetition.

p.yy, p.mo, p.dd, p.hh, p.mi, and p.ss are th input

m, d, and s are the internal representation

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;

I have a harness to supply years, months, days, hours, minutes, and seconds values (like the lteral does the,) and to get them back (as "extract" gets them) using the actual implementation and my simulation. The two approaches have never disagreed using a wide range of inputs.

The algorithm that my code shows (esp with both trunc() and float() in play) is too hard to describe in words.