Checking a table for time overlap?

user_821591 0
Viewed 16581

I have a MySQL table with the following fields:

  • name
  • starttime
  • endtime

starttime and endtime are MySQL TIME fields (not DATETIME). I need a way to periodically "scan" the table to see if there are any overlaps in time ranges within the table. If there is an event from 10:00-11:00 and another from 10:30-11:30, I want to be alerted of the presence of the time overlap.

Nothing fancy really, all I want to know whether an overlap exists or not.

I'm going to be using PHP to execute this.

range overlap time mysql sql
4 Answers

Try this, it works for me

SELECT * from Shedulles a 
where exists 
( select 1 from Shedulles b 
    where 
    a.ShedulleId != b.ShedulleId 
    and ( a.DateFrom between b.DateFrom and b.DateTo 
    or a.DateTo between b.DateFrom and b.DateTo 
    or b.DateFrom between a.DateFrom and a.DateTo ) 
    and a.DateFrom != b.DateTo 
    and b.DateFrom != a.DateTo 
);

Or this one

SELECT DISTINCT a.* FROM Shedulles a
JOIN Shedulles b 
    ON 
    a.ShedulleId != b.ShedulleId 
    and ( a.DateFrom between b.DateFrom and b.DateTo 
    or a.DateTo between b.DateFrom and b.DateTo 
    or b.DateFrom between a.DateFrom and a.DateTo ) 
    and a.DateFrom != b.DateTo 
    and b.DateFrom != a.DateTo
user_21564170
Related