MySQL Find Class Time Conflicts

Question

I have looked at a lot of solutions here, but can't get any of them to work in my situation. I'm beyond my SQL skill level here for sure.

I have a table that holds class beginning and end date/times for each class session:

Table: class_times

Columns:
time_class_id
time_end
time_start

time_end and time_start are Unix timestamps.

When a student puts a class in the cart I have to check and make sure that there are no time conflicts with any other classes in the cart. It's a time range. Could be a 4 hour class or a 2 hour class. If there is any time conflict I have to alert the student. The time_class_id is the class which could easily have a dozen rows of dates/times and different ones at that.

This is the closest I've gotten but this seems to only find conflicts where the start and end times are exactly the same:

SELECT a.time_class_id, a.time_start, a.time_end, 
b.time_class_id, b.time_start, b.time_end
FROM class_times a, class_times b
WHERE a.time_class_id in ($classes_in_cart)
AND b.time_class_id in ($classes_in_cart)
AND (a.time_class_id <> b.time_class_id
AND a.time_start >= b.time_start AND
a.time_end <= b.time_end)

Any help would be greatly appreciated.

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Juan Carlos Oropeza, Oct 23 '15 at 23:53

Gordon Linoff · Answer 1 · 2015-10-27T00:35:24.280

0

I think you want something like this:

SELECT a.time_class_id, a.time_start, a.time_end,
       b.time_class_id, b.time_start, b.time_end
FROM class_times a JOIN
     class_times b
     on find_in_set(a.time_class_id, $classes_in_cart) > 0 and
        find_in_set(b.time_class_id, $classes_in_cart) > 0 and
        a.time_class_id < b.time_class_id and
        a.time_start <= b.time_end and
        a.time_end >= b.time_start;

Two time periods overlap when one start before the second ends and the first ends after the second starts.

edited Oct 27 '15 at 00:35

answered Oct 24 '15 at 02:00

Gordon Linoff

1,242,037
58
646
786

Thanks for this. This is close to what I need, but if there are only two classes in the cart it says there is conflict but only returns one of the classes. If there are 3 classes in the cart....it returns two of them....but not the two with the conflict. I added a " GROUP BY a.time_class_id, b.time_class_id" to your query, but this did not seem to make a change either way. It does seem to find the conflict, just doesn't return both classes that are conflicting, just the one....which actually is fine if there are only two in the cart....typo at the end of your code b.time_b.time_start..fyi – Glen Oct 26 '15 at 17:58
After looking at this [link]http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap[/link] it looks like this code is working for me: SELECT a.time_class_id, a.time_start, a.time_end, b.time_class_id, b.time_start, b.time_end FROM class_times a, class_times b WHERE a.time_class_id in ($add_to_query) AND b.time_class_id in ($add_to_query) AND (a.time_class_id <> b.time_class_id AND a.time_start <= b.time_end AND a.time_end >= b.time_start) – Glen Oct 26 '15 at 18:17

MySQL Find Class Time Conflicts

1 Answers1