hi all,
I'm working on a project in which uses heavily time-indexed data. In other words, most data items represented in a DB have a "valid interval" (starting and ending time) associated with them. We are looking for efficient ways to query this data based on interval intersection, in other words, locating all data intervals which intersect with a query interval. The intersection case seems to be considerably more complex than using simple > < operators to select a range.
I have located surprisingly few discussions of efficient data storage techniques, indexing, and operations on temporal data in SQL (see discussion of the TSQL2 spec). Or, more generally, any kind of interval indexing based on a continuous variable. I did located some information which suggest using R-trees as indexing structures.
From this it seems it may be possible to write a kludge the MySQL gis tools (but in 1 dimension instead of two) in order to take advantage of the R-trees used for spatial indexing.
http://dev.mysql.com/doc/mysql/en/optimizing-spatial-analysis.html
see also Tansel, A. (1997) "Temporal Relational Data Model" IEEE Transactions on knowledge and Data Engineering, 9:3 464-479
does anybody have any additional references, experience, suggestions on these issues?
best,
-skye
ATA SpA, Lucca Italy