Re: How to store temporal info?

"Lennart Jonsson" <lelle2_at_bonetmail.com> wrote in message news:b3v7h2$1qmn64$1_at_ID-167942.news.dfncis.de...
>
> Thanx Alan, I knew I've seen references to "howto do temporal things in
> sql", before in this group, I just couldnt find them. I'll keep this one in
> a safe place ;-)

I would second the use of the latest-representable date, instead of NULL, to represent a valid time that's currently valid.

The Snodgrass book is quite good and complete. You might also be interested in the latest by Date and Darwen, "Temporal Data and the Relational Model", which you can find out more about at www.thethirdmanifesto.com.

Regards,
jag

> /Lennart
>
>
> "Alan Gutierrez" <ajglist_at_izzy.net> wrote in message
> news:slrnb65oes.rd4.ajglist_at_izzy.net...
> > In article <b3tip2$1ohils$1_at_ID-167942.news.dfncis.de>, Lennart Jonsson
> wrote:
> >
> > > create table resourcecategory_history (
> > > category integer not null,
> > > resource integer not null,
> > > start timestamp not null,
> > > stop timestamp,
> > > primary key (category, resource, start)
> > > foreign key (category) references resourcecategory
> > > foreign key (resource) references resource
> > > )
> >
> > > The second alternative is appealing because it better reflects the
> actual
> > > situation. It is however much more difficult to ask simple questions
> like
> > >
> > > select category, count(*) from result group by category
> > >
> > > since temporal info has to be dealt with
> > >
> > > select rch.category, count(*)
> > > from result r, resultcategory_history rch
> > > where r.resourceid = rch.resource and
> > > rch.start >= r.resulttime and
> > > (rch.stop is null or rch.stop < r.resulttime)
> > > group by rch.category
> > >
> > > besides one will have to add some checks (possibly via triggers) that
> > > overlapping does not occur
> >
> > > Having to use triggers always kinda rings a bell that there is something
> > > fishy with the design. How is these designs normally done? Any thoughts,
> > > anyone?
> >
> > I'm using the *_history desgin, with a slight change. Rather than
> > use a null value for something not yet stopped, I use a the value
> > 9999/12/31 00:00:00. That way your query becomes:
> >
> > SELECT Rch.category, COUNT(*)
> > FROM Result, Resultcategory_History Rch
> > WHERE Result.resourceid = Rch.resource
> > AND Result.resulttime BETWEEN Rch.start AND Rch.stop;
> > GROUP BY Rch.category
> >
> > Thers is a book by a fellow named Snodgrass that I am slowly working
> > through, that talks about this concept, which I believe is called a
> > valid-time table in his book.
> >
> > Developing Time-Oriented Database Applications in SQL
> > http://www.amazon.com/exec/obidos/ASIN/1558604367
> >
> > --
> > Alan Gutierrez - ajglist_at_izzy.net
> > http://khtml-win32.sourceforge.net/ - KHTML on Windows
>
>
Received on Mon Mar 03 2003 - 15:18:49 CET