Check if two date intervals intersect

Question

I need to check in MySQL to see if two date intervals intersect. To explain better my issue: I have an event management module. If we have an event that was added like this:

start date : '2013-09-09 08:00:00'

end date : '2013-09-09 10:00:00'

and now I want to add another event that's like this:

case A:

start date : '2013-09-09 09:00:00'

end date : '2013-09-09 11:00:00'

OR like this :

case B:

start date : '2013-09-09 07:00:00'

end date : '2013-09-09 12:00:00'

I shouldn't be able to do this because an event was already added in that time interval (08-10)

For the first example (case A), I solved the issue by doing this :

SELECT * FROM `events` as e 
    where 
    '2013-09-09 08:00:00' between e.ev_date_start and e.ev_date_end -- date start
    OR
    '2013-09-09 11:00:00' between e.ev_date_start and e.ev_date_end -- date end

But for the second case (case B) I'm having trouble figuring it out...

Answer 1

The idea is to check if they DO NOT intersect, and then negate.

NOT ('2013-09-09 08:00:00' >= e.ev_date_end OR e.ev_date_start >= '2013-09-09 11:00:00')

which is logically equivalent to

'2013-09-09 08:00:00' < e.ev_date_end AND e.ev_date_start < '2013-09-09 11:00:00'

Answer 2

Test just that the start times of already assigned time blocks or the to be assigned time block do not fall between each other.

select * from `events` as e 
where '2013-09-09 08:00:00' between e.ev_date_start and e.ev_date_end
or e.ev_date_start between '2013-09-09 08:00:00'and '2013-09-09 11:00:00'