Re: ERD for time-based relationships

But if you use '9999-12-31 23:59:59.99999' (this is ISO-8601 format and the only one allowed in Standard SQL), then all your temporal math gets harder. COALESCE(end_date, CURRENT_TIMESTAMP) is a small price to pay for correct and most current information. Suddenly, you are making a real date and time into Eternity and it is not.

I am not worried about the indexes because the key is PRIMARY KEY (foo_id, start_date) and does not involve end_date at all. If I do tree index on end_date, I think that most RDBMS products can locate them all on one limb of the tree. Cetrtainly if I use hashing, they all fall into the same hash bucket and I gain a huge boost in searching.

As an aside, when I worked in COBOL systems in a State Prison System, we had a field for "expected date of release" in the inmate records. We would put in a real date (with a four digit year! We knew about Y2K) or use '8888-88-88' for a "life Sentence" or '9999-99-99' for a "Death Sentence" -- two very different kinds of unspecified times!! Received on Wed Aug 20 2003 - 23:05:37 CEST