Finding all date ranges (which may be open-ended) that overlap with a given range

2011 May 17 (Tuesday), 7:43PM

Given a database table listing events and their date ranges: events { id, start_date, end_date, … }, where either or both of the dates can be null, how is one to find all of the events that fall within (even partially) a given date range? (This is pretty much what Kieran Benton asked on Stack Overflow, with the addition of the nullability.)

There are twelve possibilities for ranges with respect to the given range A-B:

Possible ranges within a given range. Note that the gradients indicate null start or end dates. Source SVG

The red ranges are the ones that should be included in the result; the orange ones should be omitted. It’s easier to query for the smaller set, which satisfy the following conditions:

	> A	< A	> B	< B	NULL
Start Date		x
End Date		x
Start Date					x
End Date		x
Start Date			x
End Date			x
Start Date			x
End Date					x

Which corresponds to the following SQL:

SELECT * FROM events
WHERE NOT (
  (start_date IS NOT NULL AND start_date < :A AND end_date IS NOT NULL AND end_date < :A)
  OR (start_date IS NOT NULL AND start_date > :B AND end_date IS NOT NULL AND end_date > :B)
  OR (start_date IS NULL AND end_date IS NOT NULL AND end_date < :A)
  OR (start_date IS NOT NULL AND start_date > :B and end_date IS NULL)
)