This post is about comparing two timespans and determining whether they overlap at all. Suppose you have a table of timespans similar to the following abstract example:
CREATE TABLE timespans ( id INT IDENTITY PRIMARY KEY, starttime DATETIME NOT NULL, endtime DATETIME NOT NULL ) |
A common task is to determine what expression determines whether two given timespans overlap. In this example, say @id1 and @id2 are the two timespans.
A straightforward approach might lead you to consider a number of different scenarios to check. I’ve seen the following checks a couple times:
- timespan @id1 contains the start time of timespan @id2
- timespan @id1 contains the end time of timespan @id2
- timespan @id2 contains the start time of timespan @id1
- timespan @id2 contains the end time of timespan @id1
These are four checks which result in 8 comparisons. It turns out that the last check is extraneous and so only 6 comparisons are needed.
I find it helpful to first determine whether the timespans do not overlap by doing the following checks:
- timespan @id1 starts after timespan @id2 ends
- timespan @id2 starts after timespan @id1 ends
This only needs two checks but each check is one comparison for a total of 2 comparisons. This leads to queries that contain expressions like the one used here:
SELECT 1 FROM timespans AS timespan1, timespans AS timespan2 WHERE timespan1.id = @id1 AND timespan2.id = @id2 AND NOT timespan1.endtime < timespan2.starttime AND NOT timespan2.endtime < timespan1.starttime |
This query will return 1 if the two timespans @id1 and @id2 overlap in some way.
