How to determine if a date range occurs any time within another date range?

Question

I have an Event table that specifies a date range with start_date and end_date fields. I have another date range, specified in code, that defines the current week as 'week_start' and 'week_end'.

I'd like to query all Events for the week. The cases seem to be:

• Event begins and ends within the week
• Event begins before the week, but ends within the week
• Event begins within the week, but ends after the week
• Event begins before the week and also ends after the week
• Events that neither reside within, nor overlap the week at all are ignored

I'm attempting to come up with a query that can handle all these cases. So far I've only been able to get cases that handle the week overlaps, or events that are fully internal; Essentially, too many records, or none at all.

Answer 1

(event.start BETWEEN week.start AND week.end)
OR
(week.start BETWEEN event.start AND event.end)

In simple words, either a week starts during the event, or an event starts during the week.

Let's check it:

Event begins and ends within the week

The event starts during the week.

Event begins before the week, but ends within the week

The week starts during the event.

Event begins within the week, but ends after the week

The event starts during the week.

Event begins before the week and also ends after the week

The week starts during the event.

Note that BETWEEN in expressions above is used just for the sake of brevity.

Strict expression looks like this:

(event.start >= week.start AND event.start < week.end)
OR
(week.start >= event.start AND week.start < event.end)

, provided that week.end is a week.start + INTERVAL 7 DAY.

I. e. if you week starts of Sun, 0:00:00, then it should end on next Sun, 0:00:00 (not on Sat, 0:00:00)

This expression looks more complex than the one which is commonly used:

event.start < week.end AND event.end > week.start

, but the former is more efficient and index friendly.

See these articles in my blog for performance comparisons:

• Overlapping ranges: SQL Server
• Overlapping ranges: MySQL
• Overlapping ranges: Oracle