MySQL get list of start time and end time conflict

Question

Need help for MySQL query for retrieving record of class schedule that is conflict in the given time.

Ex.

SchedID StartTime EndTime
1       09:00:00  13:00:00
2       08:30:00  10:00:00
3       11:00:00  15:00:00
4       07:30:00  08:30:00
5       11:30:00  13:00:00

I would like to retrieve the list that is conflict in this given time Start Time = 09:00:00 End Time = 11:00:00

The record will yield following result:

SchedID StartTime EndTime
1       09:00:00  13:00:00
2       08:30:00  10:00:00
3       11:00:00  15:00:00

Thank you.

possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Uueerdo, Sep 24 '15 at 16:54

mynawaz · Answer 1 · 2015-09-24T17:27:01.583

0

Try this

SELECT DISTINCT a.schedid, a.starttime, a.endtime
FROM
    tbl_name a, tbl_name b 
WHERE ((b.starttime > a.starttime AND b.starttime < a.endtime) 
    OR (b.endtime > a.starttime AND b.endtime < a.endtime))
ORDER BY schedid

edited Sep 24 '15 at 17:27

answered Sep 24 '15 at 17:11

mynawaz

1,599
1
9
16

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

i just found my answer here Determine Whether Two Date Ranges Overlap

Heres my query:

SELECT * FROM tableName WHERE (start_time < givenEndTime) 
AND (end_time > givenStartTime);

This works perfectly fine for me.

edited May 23 '17 at 12:06

Community

1
1

answered Sep 25 '15 at 00:53

Roelzkie

61
11

MySQL get list of start time and end time conflict

2 Answers2