Danish M. Danish M. - 7 months ago 29
SQL Question

Checking a table for time overlap?

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.

Answer

This is a query pattern for which I found the answer many years ago:

SELECT *
FROM mytable a
JOIN mytable b on a.starttime <= b.endtime
    and a.endtime >= b.starttime
    and a.name != b.name; -- ideally, this would compare a "key" column, eg id

To find "any overlap", you compare the opposite ends of the timeframe with each other. It's something I had to get a pen and paper out for and draw adjacent ranges to realise that the edge cases boiled down to this comparison.


If you want to prevent any rows from overlapping, put a variant of this query in a trigger:

create trigger mytable_no_overlap
before insert on mytable
for each row
begin
  if exists (select * from mytable
             where starttime <= new.endtime
             and endtime >= new.starttime) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Overlaps with existing data';
  end if;
end;