3

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)
2
  • What do you mean with "intersection"? Do you have a fixed timestamp that you want to compare with the (timeform, timeuntil) range? Or you want to compare the ranges between rows? If you added a few sample rows and the expected outcome, it would be much better,. May 12, 2015 at 16:33
  • Questions like this are frequently best understood with DDL (CREATE TABLE blah (field1 type1, field2....)); and DML (INSERT INTO blah VALUES(value1, value2...); Then, from that data, provide us with the result you want and the logic you used to get it. p.s. welcome to the forum. May 12, 2015 at 16:41

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.