Re: SQL for intervals

>> I just start learning sql. I wonder whether you can help me with this: <<

The real lesson here is that the nature of time is a continuum, Remember Zeno, Einstein and all those guys? You need to model the start and end times of a state or an event, something like this:

CREATE TABLE Events
(event_id INTEGER NOT NULL,
 start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,  PRIMARY KEY (event_id, start_time),
 end_time DATETIME, -- null means still active  CHECK (start_time < end_time),
..);

Now use predicates like this:

  my_date BETWEEN start_time

                AND COALESCE(end_time, CURRENT_TIMESTAMP),

and a VIEW that finds the rows where (end_time IS NULL).

You will find that having a Calendar table will be useful, too.

What you are asking is how to construct the table you should have had in the first place. Also, the only format allowed for dates in Standard SQL (and other ISO standards) is 'yyyy-mm-dd' because it is not ambigous. Received on Sat Dec 02 2006 - 15:28:24 CET