How to fill in all periods between two dates in MySQL?

I have the following situation, where I have some individuals with a start and an end date:

ID | start_date | end_date
1    2015-02-15   2015-04-20
2    2015-03-10   2015-06-15
...   ...            ...

Now, I need to derive a table with the individuals and all consecutive 30 day periods between their start and end dates (starting from the start_date). The result should look like this:

ID | period | from_date   | to_date
1     1       2015-02-15    2015-03-17
1     2       2015-03-18    2015-04-17
2     1       2015-03-10    2015-04-09
2     2       2015-04-10    2015-05-10
2     3       2015-05-11    2015-06-10

Do you have any idea how to create such a table in a clever way in MySQL? If MySQL is too cumbersome for such a data manipulation, R or Excel will work for me as well.

Solution

You can generate a range of numbers, then cross join that range with all the records, adding as many groups of 30 days to that row as the number returned.

Something like this (not tested so please excuse any typos):-

SELECT a.id, b.aNum, DATE_ADD(a.start_date, INTERVAL (b.aNum * 30) DAY) AS from_date, DATE_ADD(a.start_date, INTERVAL ((b.aNum + 1) * 30) DAY) AS to_date
FROM sometable a
CROSS JOIN
(
    SELECT tens.aCnt * 10 + units.aCnt AS aNum
    FROM
    (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
    CROSS JOIN
    (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
) b
WHERE  DATE_ADD(a.start_date, INTERVAL (b.aNum * 30) DAY) <= end_date

This version only works for up to 100 groups of 30 days but can easily be expanded (but will get slower the more groups you cope with)