Re: 'infinity'::Interval should be added

On Fri, 14 Dec 2018 at 15:16, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Why? I consider it somewhat of a wart that timestamps allow infinity
> - it adds special case coding all over the place. Allowing intervals
> to be infinite as well seems like it'll just create more special cases
> without really solving anything.
>

Au contraire, it eliminates special case coding all over the place. For
example, if I have authorizations that don't expire, I can just give them
an expiration of 'infinity' and operations like "today's date less than
expiration" just work; I don't have to say "expiration null or greater than
today's date".

I would be interested if you have an example where the ability of
date/timestamp values to be infinite adds special case coding.

If we allow intervals to be infinity, then we will eliminate more special
cases. Right now, whenever timestamp values are subtracted, the programmer
has to consider what happens if an operand is infinity and handle that case
specially. With infinite intervals allowed, this problem is reduced. For
example, how long until expiry? Right now:

CASE WHEN expiry = 'infinity' THEN NULL WHEN expiry = '-infinity' THEN '0'
ELSE expiry - current_timestamp END

With the proposal:

expiry - current_timestamp

And another improvement: infinity is represented by 'infinity' rather than
the somewhat ambiguous NULL.

Just for definiteness, I believe the following are the correct semantics
for subtraction involving infinity:

∞ - t = ∞ (t ≠ ∞)
-∞ - t = -∞ (t ≠ -∞)
∞ - ∞ = err
-∞ - -∞ = err

I'm not sure whether "err" should be an error, as it is now ("cannot
subtract infinite dates") and like division by 0, or NULL.

The wart I'm worried about is subtraction of infinite dates. Right now
dates subtract to give integers; and there are no infinite integers. All
the clever solutions to this I have right now involve making highly
backward-incompatible changes.