CODEWITHSUNDEEP
mysqldatetime
mauris
mauris

Reputation: 43619

Detect if two timings clash / intersect or not

I am currently developing an event management system where the database stores time slots as such:

SlotId | DateTime            | Duration  
-------+---------------------+---------------
 1     | 2011-06-06 15:00:00 | 5.0
 2     | 2011-06-06 14:00:00 | 3.0
 3     | 2011-06-07 11:00:00 | 8.0

Note that Duration is a DOUBLE in hours and DateTime is a TIMESTAMP.

In the table, we can clearly see that time slots 1 and 2 clashes, whereas time slots 2 & 3 and time slots 1 & 3 doesn't clash. How can we write the SQL statement to detect clashes between the two time slots?

Upvotes: 0

Views: 648

Answers (1)

Paul Wheeler
Paul Wheeler

Reputation: 20140

Maybe I'm confused, but it looks like all three of those time slots overlap. Since they go From 11:00 - 19:00, 14:00 - 17:00, and 15:00 - 20:00 respectively (in reverse of your order, chronologically by start time). But if I understand your table correctly here is the code to find the overlaps 

SELECT CONCAT(T1.SlotId,  ' Overlaps ', T2.SlotId)
FROM YourTable AS T1 JOIN YourTable AS T2 
WHERE T1.SlotId <> T2.SlotId AND 
    T1.DateTime <= T2.DateTime AND 
    DATE_ADD(T1.DateTime,INTERVAL SEC_TO_TIME(T1.Duration * 3600) HOUR_SECOND) > T2.DateTime

Upvotes: 1

Related Questions