Querying 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.

Date Range Overlaps

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.

Starts Ends Has Overlap start < range_end end > range_start
Before Before No Yes No
Before During Yes Yes Yes
Before After Yes Yes Yes
During During Yes Yes Yes
During After Yes Yes Yes
After After No No Yes

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.

Sutherland Boswell
Sutherland Boswell

I'm a web developer in Montgomery, AL with a degree in economics and a love for skateboarding, photography, NASCAR, and US soccer.