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.