Checking a table for time overlap?

by

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;

More Related Contents:

  1. How to populate a table with a range of dates?
  2. How to set a maximum execution time for a mysql query?
  3. INSERT INTO SELECT statement with more columns which is not in another table
  4. MySQL how to fill missing dates in range?
  5. Left Outer Join doesn’t return all rows from my left table?
  6. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  7. 1052: Column ‘id’ in field list is ambiguous
  8. How to split the name string in mysql?
  9. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  10. Cumulative sum over a set of rows in mysql
  11. Set NOW() as Default Value for datetime datatype?
  12. SQL keys, MUL vs PRI vs UNI
  13. Add Auto-Increment ID to existing table?
  14. MySQL SELECT DISTINCT multiple columns
  15. How to copy a row and insert in same table with a autoincrement field in MySQL?
  16. SQL DELETE with JOIN another table for WHERE condition
  17. How to skip certain database tables with mysqldump?
  18. “where 1=1” statement [duplicate]
  19. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  20. how to cast the hexadecimal to varchar(datetime)?
  21. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  22. How to get a list of months between two dates in mysql
  23. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  24. Postgres LEFT JOIN with WHERE condition
  25. How to format number with “.” as thousands separator and “,” as decimal separator?
  26. Using DISTINCT and COUNT together in a MySQL Query
  27. How to check if field is null or empty in MySQL?
  28. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  29. Deleting a row based on the max value
  30. MySQL Multiple Joins in one query?

Leave a Comment