mysql重叠的部分不要,重叠日期范围MySQL

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.

解决方案

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

WHERE

< endDate

AND

> startDate