Mysql query to see if a booking will conflict with another booking.

Question

I have a table which stores a bunch of books. Customers can book appointments at for any time during the day. I want to make sure that a booking doesnt already exist that will conflict with the new booking.

So I want to find the booking on that date and check that the start and end times dont overlap. I tried doing this with a sub select query to get the bookings from that day and then see if those times overlapped but it didnt seem to be working.

Here is the query I am using. (Status and active are some other fields I need). I find the relevant dates and then see if 8am is between those results individual start/end times or is 9.40am also falls between start/end times.

SELECT event_date, starttime, endtime, status, active 
FROM (SELECT event_date, starttime, endtime, status, active FROM bookings WHERE event_date='2013-07-21' AND status != 'cancelled' AND active !=0 LIMIT 1) AS q1
WHERE ('8:00:00' BETWEEN q1.starttime AND q1.endtime) AND ('9:40:00' BETWEEN q1.starttime AND q1.endtime)

Is this the best method to use? Thanks

Answer 1

To check if one range overlaps another it's easier to think first when it won't overlap and then reverse the condition. It's obvious that:

Two ranges (A,B) don't overlap if A starts after B ends, or if A ends before B starts.

Turning this to SQL you get the following condition for finding an overlapping range:

... WHERE NOT ('8:00:00' > q1.endtime OR '9:40:00' < q1.starttime)

If you want you can rewrite that as:

... WHERE q1.endtime > '8:00:00' AND q1.starttime < '9:40:00'

This way you handle all the corner cases, like one interval completely containing the other.