Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!16g2000cwy.googlegroups.com!not-for-mail
From: "-CELKO-" <jcelko212@earthlink.net>
Newsgroups: comp.databases.theory
Subject: Re: SQL for intervals
Date: 2 Dec 2006 06:28:24 -0800
Organization: http://groups.google.com
Lines: 28
Message-ID: <1165069704.054606.246440@16g2000cwy.googlegroups.com>
References: <1165015487.535910.110510@j44g2000cwa.googlegroups.com>
NNTP-Posting-Host: 63.246.191.229
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1165069709 21842 127.0.0.1 (2 Dec 2006 14:28:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 2 Dec 2006 14:28:29 +0000 (UTC)
In-Reply-To: <1165015487.535910.110510@j44g2000cwa.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.8) Gecko/20061025 Firefox/1.5.0.8,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 16g2000cwy.googlegroups.com; posting-host=63.246.191.229;
   posting-account=EzrcbQ0AAACimcwOd0QEJhKx5W98nxwt
Xref: news.f.de.plusline.net comp.databases.theory:40264

>> 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.