> My table represents employee absence starting and ending dates.
> If end day is not yet known, it is represented by NULL value.
> My query should threat unknown value as never ending absence to return
> estimated number of work days.
> Infinity date value is missing in SQL standard.
> I do'nt know any other good way to represent missing ending date.
> > There is a value "infinity" for timestamps, but unfortunately not for
> > dates. Otherwise, I'd suggest that you use that instead.
> I tried to use
> timestamp 'infinity':: date
> but this does not work if both b and d are infinity since
> select timestamp 'infinity':: date<=timestamp 'infinity':: date
> returns null.
This might explain why you are getting null;
logs=# select 'infinity'::date;
ERROR: invalid input syntax for type date: "infinity"
logs=# select 'infinity'::timestamp;
timestamp
-----------
infinity
(1 row)
apparently date doesn't know anything about infinity. However, from what I've read in my "SQL for
smarties" book regarding temporial database design, unknown future dates were stored as:
'9999-12-31'
Would this help, since any enddate with this value would be be enterpreted as an enddate that has
not yet occured? when you arrive at the date for records effective period to close just update
the enddate to the today's date.
Regards,
Richard Broersma Jr.