James B. Byrne wrote:
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after. Both are datetime types
> containing values normalized to utc. You see where this is going.
>
> The issue is what to enter when the value is known to be unknown, as
If it's unknown use null.
> in some indeterminate future date, which may be never.
That's not unknown that's "in the future".
> I read that
> relational set values should never be null, as null is indeterminate
> for WHERE clauses and may result in unexpected results.
Only if you use it to mean something other than unknown. If you have an
event that starts '2001-01-01 01:01:01+01' and ends "null" then you can
confidently say "don't know" as to how long that event is.
> On the
> other hand, setting some artificially excessive future date seems in
> its place seems, to me, to have its own problems.
Which is where you reach for the handy datetime literals as described below:
select 'infinity'::timestamp without time zone;
select '-infinity'::timestamp without time zone;
--
Richard Huxton
Archonet Ltd
