Split overlapping dates into different records

Question

I am developing a PHP + MySQL application. I have a table which contains start date and end date of periods. Periods can overlap. Example:

ID   Start       End
1    01/05/2015  31/05/2015
2    01/06/2015  30/06/2015
3    15/06/2015  25/06/2015 (this record overlaps record with ID 2)
4    17/06/2015  22/06/2015 (this record overlaps records with IDs 2 and 3)

I need to select all records, order by start date and end date, but also split overlapping records and keep their IDs. What i mean is i need to get this in the end:

ID   Start       End
1    01/05/2015  31/05/2015
2    01/06/2015  14/06/2015
3    15/06/2015  16/06/2015
4    17/06/2015  22/06/2015
3    23/06/2015  25/06/2015
2    26/06/2015  30/06/2015

I can do this by selecting all periods and then sorting/splitting them in PHP, but i am wondering if i can do it on database layer?

Can someone at least point me in the right direction, please?

Answer 1

I think you have to do it PHP (or maybe a db procedure, but I wouldn't do it on db layer), it will be easier. There are many combination which you have to consider and they cannot fit into single SQL statement.

Here is the example how to discover overlaps (at least this is how I can help you):

SELECT  t1.id, t1.start, t1.id, t2.id
FROM    T t1, T t2
WHERE   (t2.start BETWEEN t1.start AND t1.end
        OR t2.end BETWEEN t1.start AND t1.end)
        AND t2.id > t1.id

DEMO HERE