Re: How to store temporal info?
Date: Mon, 03 Mar 2003 22:03:05 GMT
>> "Lennart Jonsson" <lelle2_at_bonetmail.com> wrote in message >> news:b407bq$1qd9l2$1_at_ID-167942.news.dfncis.de... >>> On Mon, 03 Mar 2003 14:18:49 +0000, John Gilson wrote:
>> Two issues come to mind Lennart: >> >> 2. Consider the queries to get a resource R's category at time T when >> there's a start-time and when there's both a start-time and end-time. >> When there's just a start-time, the query might be >> >> SELECT RC1.*, :T >> FROM ResourceCategories >> WHERE resource = :R AND >> since = (SELECT MAX(since) >> FROM ResourceCategories >> WHERE resource = :R AND since <= :T)
I had a since field myself. I got rid of it. I am happy.
>> with both a start-time and end-time one can write >> >> SELECT *, :T >> FROM ResourceCategories >> WHERE resource = :R AND :T BETWEEN start_time AND end_time >> >> Similarly for queries that must calculate the duration of particular >> resource-category associations.
> 1 if end_time is allowed to be null (indicating that we dont know when it
> will sease), one will have to deal with that in the queries. I.e.
> something similar to:
> WHERE resource = :R AND :T > start_time AND (end_time is null or :T <
Then don't use NULL. It is often best to use a value for not-applicable or unknown on an indexed column since most database engines will perform better on an indexed column when the column is defined NOT NULL.
And as far as a since field goes, a subquery will be run for each row in your query, SELECT MAX. BETWEEN can use an index if need be.
> which is ok, but less elegant than "between" (and probably less
> efficient, I guess)
> 2 changing the category for a resource means adding a new row, but also
> that we will have to locate and update the "current row". With "the since"
> case, it is sufficient to add a new row
Yup. You pay for it at UPDATE, rather than for each row during a query with SELECT MAX.
> 3. some kind of check has to be implemented to assure that we are not
> creating "overlaps" for a resource. In the "since" case, overlaps are
Is that necessary? An SQL script woud read:
BEGIN; UPDATE ResourceCategory
SET stop = CURRENT_TIMESTAMP
WHERE resource = :R
AND stop = '9999/12/31 00:00:00';
INSERT INTO ResourceCategory (resource, whatever, start, stop) VALUES (:R, :W, CURRENT_TIMESTAMP, '9999/12/31 00:00:00'); COMMIT; In PostgreSQL CURRENT_TIME will hold the same value for the duration of the transaction. Hard to go wrong. Me thinks you worry to much.
I like my new hammer better. What's that honey? Wash the dishes? Sure!
*bang* *bang* *bang*
-- Alan Gutierrez - ajglist_at_izzy.net http://khtml-win32.sourceforge.net/ - KHTML on WindowsReceived on Mon Mar 03 2003 - 23:03:05 CET