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.
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
end column, and the range we're querying is
SELECT * FROM events WHERE start <= $range_end AND end >= $range_start
Of course you may want to switch to using
> instead of
>= depending on whether or not you want to allow dates/times to touch.
Now let's look at why this works.
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.
|Starts||Ends||Has Overlap||start < range_end||end > range_start|
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.