Re: How to store temporal info?

In article <b40ea5$1qdp76$1_at_ID-167942.news.dfncis.de>, Lennart Jonsson wrote:

> On Mon, 03 Mar 2003 19:43:53 +0000, John Gilson wrote:
>

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

> The main objections (for my scenario) that I have against start and stop is that:

> 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 <
> end_time)

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)

Both.

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

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.

> Since I am equipped with a new hammer, everything suddenly looks like
> nails :-)

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 Windows