Kat Kat - 1 year ago 58
MySQL Question

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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download