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)
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.