Hi all,
I have a table that stores a time interval as two timestamps, and want to construct the most efficient query to check whether two intervals in the table overlap. The naive way, using two range conditions [1], seems to choke for large tables. The M5 SQL manual mentions an OVERLAPS operator that, as far as I've read [2], seems to use an optimized interval join algorithm.
Can anyone clarify:
1. How to convert a start timestamp and an end timestamp into an interval type 2. How to use the OVERLAPS operator
Many thanks. Brien
[0] create table intervals as ( id bigint, series_id bigint, start_time timestamp, end_time timestamp )
[1] select a.id as a_id b.id as b_id from intervals a inner join intervals b on b.series_id = a.series_id and b.id < a.id and b.start_time < a.end_time and a.start_time < b.end_time
[2] http://monetdb.cwi.nl/XQuery/Documentation/StandOff-Extension.html