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.
