Database query for overlapping date ranges for events, appointments, and more

When building an application that handles events, appointments, or any sort of data with a start and end time, you’ll eventually find that you need to do a MySQL query for events that overlap a date range.

The two most common uses include:

• Allow users to filter events by date range
• Ensuring calendar appointments don’t overlap

If you’ve never written a query to do this before, it can be confusing and easily overcomplicated. Thankfully it’s actually pretty simple.

The Solution

I figure some of you will just want the solution, so we’ll start there and work backwards. Assume we have a table with a start and end column, and the range we’re querying is $range_start and $range_end.

SELECT *
FROM events
WHERE start <= $range_end
AND end >= $range_start

Of course you may want to switch to using < and > instead of <= and >= depending on whether or not you want to allow dates/times to touch.

Now let’s look at why this works.

Possible scenarios

There are six possible scenarios for a date range to fall under relative to another date range. In the illustration below you’ll see how they compare to a selected range shown in light blue.

As you can see, the events that overlap our range are 1, 3, 4, and 5. The start of each of these is before the end of our range, and the end of each is after the start of our range.

Only 2 and 6 don’t overlap. The end of 2 is before the start of our range, and the start of 6 is after the end of our range, so as expected both will fail to match our query above.

Here’s a table of all the scenarios, whether or not they should overlap our range, and how it compares to our two conditions.

Hopefully this has helped you understand how to find database entries with an overlapping date range. As I mentioned before it’s easy to overcomplicate things, but once you understand it’s actually pretty simple.