MySQL intersection on one table with time type

MySQLtime

I've a table with 4 columns: id (int), name (varchar), timefrom (time), timeuntil (time)

Now I would like to have all combinations where they have an intersection on the timefrom to timeuntil.

My current solution is that I load all the records and loop it in php for every full hour… but this costs me much performance.

Any Ideas, is this possible to solve in a Select?

I want to know which presences are sharing the same timefrom, timeuntil As an example: 

#1 / whole day / 06:00:00 / 18:00:00 
#2 / morning   / 07:00:00 / 12:00:00 
#3 / afternoon / 13:00:00 / 18:00:00

I would like to have the result: 

#1, #2, #3   (the whole day shares the times with morning, afternoon) 
#2, #1       (the morning shares the times with the whole day) 
#3, #1       (the afternoon shares the times with the whole day)

Best Answer

It seems you need a self join with a non-equality condition that checks for overlaping ranges. The output can be in different formats. Depending on your needs, the easiest would be a 2-column output:

SELECT a.id AS a_id, b.id AS b_id
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
ORDER BY a.id, b.id ;

Output would be (2 columns):

#1    #2   (the whole day shares the times with morning) 
#1    #3   (the whole day shares the times with afternoon) 
#2    #1   (the morning shares the times with the whole day) 
#3    #1   (the afternoon shares the times with the whole day)

To get the rows combined, you can use GROUP_CONCAT():

SELECT a.id AS a_id, 
       GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ' ') AS b_ids
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil 
GROUP BY a.id
ORDER BY a.id ;

Output (2-columns):

#1    #2 #3   (the whole day shares the times with morning, afternoon) 
#2    #1      (the morning shares the times with the whole day) 
#3    #1      (the afternoon shares the times with the whole day)

If you want one row per a.id and multiple columns, you'll need dynamic SQL.

Related Question