Are there any MySQL functions to get all rows with with a start or end date that fall between a given start and end date?

Question

I have a table of events with a recorded start and end time as a MySQL DATETIME object (in the format YYYY-MM-DD HH:MM:SS. I want to find all events that occur in a specific date range. However, events can span multiple days (and go outside of my date range, but I want to return them if they even overlap by 1 second or more with my date range).

Suggestions?

Answer 1

SELECT *
FROM table
WHERE startdate >= 'starting date' AND startdate < 'ending date'
    OR enddate >= 'starting date' AND enddate < 'ending date'

should work for you.

Make sure you specify 'starting date' and 'ending date' with the time included.

'2008-01-01 00:00:00'' AND '2008-01-31 23:59:59'

This will help to avoid errors where dates are the same, but your time falls within the interval by a few hours, minutes, or seconds.

Answer 2

This will find every event that is completely contained inside the range:

SELECT * FROM table WHERE start_date BETWEEN start_of_range AND end_of_range 
                      AND stop_date  BETWEEN start_of_range AND end_of_range

This will find any events where any part of the event overlaps any part of the range:

SELECT * FROM table WHERE start_date <= end_of_range 
                      AND stop_date  >= start_of_range

Answer 3

Basically, you can use regular comparisons -- the ones above should work -- the trick is to check all the different cases that can occur.

A) events with an ending date within the range

B) events with a starting date within the range

C) events with both starting and ending dates within the range

D) events with both starting and ending dates outside the range, but overlapping it

Robert's answer is a good one, but it doesn't take into account case D, where the event starts before the range and ends after the range.

Answer 4

The answers by @Bill the Lizard and @Robert Gamble are correct for the question as asked, but I do wonder if you're asking what you think you are... If you're looking for overlapping events then you need to take into account events longer than your search range.

           Monday   Tuesday   Wednesday   Thursday

Search:                |-----------|

Shopping                       |-----|              Found OK
Eating              |--------|                      Found OK
Stack Overflow |---------------------------------|  Not found!

If you wanted to include SO, you'd do:

SELECT * FROM table WHERE (start_date < end_of_range AND end_date > start_of_range)

Answer 5

Llya, Roberts answer with,

SELECT * FROM table WHERE start_date <= end_of_range AND stop_date >= start_of_range

works fine with,

D) events with both starting and ending dates outside the range, but overlapping it

??