Overlapping date range MySQL

Question

Hi guys,

I have the following data;

ID  startDate               endDate
-----------------------------------------------
1   2010-03-01 10:00:00     2010-03-01 12:00:00
2   2010-03-01 12:30:00     2010-03-01 15:30:00
3   2010-03-01 15:30:00     2010-03-01 18:30:00

What I want to do is check that a start and end date don't fall inside the startDate and endDate ranges in my data.

So for example, the following would be OK;

startDate               endDate
-----------------------------------------------
2010-03-01 12:00:00     2010-03-01 12:30:00
2010-03-01 18:30:00     2010-03-01 21:00:00

but the following dates would fail, as they would overlap;

startDate               endDate
-----------------------------------------------
2010-03-01 09:00:00     2010-03-01 13:00:00 (overlaps ID 1)
2010-03-01 10:30:00     2010-03-01 11:00:00 (overlaps ID 1)
2010-03-01 18:00:00     2010-03-01 19:00:00 (overlaps ID 3)

I'm pulling my hair out because I can get one or two of the above 3 test date ranges to fail but not all of them.

I am using MySQL.

Answer 1

A query to select overlaps (I'd name the columns startTime & endTime though, as Time seems important...):

WHERE 
  (<start> BETWEEN startDate AND endDate AND <start> != endDate)
  OR 
  (<end>   BETWEEN startDate AND endDate AND <end> != startDate)
  OR
  (<start> <= startDate AND <end> >= endDate ) -- whole existing range falls in new range

---

Edit: Silly me, use:

<start> < endDate
AND
<end> > startDate

Answer 2

This is probably not the best method, but maybe you can gleam something from this example. This would return a result only if they overlapped, and I would probably use this in a not exists query.

select 1 from myTable
where 
('03/01/2010 09:00:00' between startDate and endDate)
or
('03/01/2010 13:00:00' between startDate and endDate)
or 
(startDate between '03/01/2010 09:00:00' and '03/01/2010 13:00:00')
or
(endDate between '03/01/2010 09:00:00' and '03/01/2010 13:00:00')

Per Wrikken's answer, you probably need to check that the values aren't exact matches as well.

Answer 3

Overlapping entries:

SELECT t1.Id, t2.Id, t1.StartDate, t1.EndDate, t2.StartDate, t2.EndDate
FROM Table t1, Table t2
WHERE t1.ID <> t2.Id
AND (t1.StartDate between t2.StartDate and t2.EndDate
    OR
     t1.EndDate between  t2.StartDate and t2.EndDate)