Re: Have I found an interval arithmetic bug?

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.postgresql.org/message-id/1162319515.20050202141132@mail.r
> 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.postgresql.org/docs/13/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

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.)

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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