Need an array of date blocks from MySql Database

Question

Ok, I have a database table of rows with a StartDate and an EndDate. What I need to do is return blocks of consumed time from that.

So, for example, if I have 3 rows as follows:

RowID  StartDate   EndDate
1      2011-01-01  2011-02-01
2      2011-01-30  2011-02-20
3      2011-03-01  2011-04-01

then the blocks of used time would be as follows:

2011-01-01 to 2011-02-20 and 2011-03-01 to 2011-04-01

Is there an easy method of extracting that from a MySql database? Any suggestions welcome!

Answer 1

Look at the diagram below which represents some overlapping time periods

X----|              |--------|         |------X
    |-------X      X------------X
                         |----|     X----|

The beginning or end of any contiguous time period, marked with an X doesn't fall inside any other time period. If we identify these times we can make some progress.

This query identifies the boundaries.

SELECT boundary FROM
(

-- find all the lower bounds
    SELECT d1.StartDate AS boundary, 'lower' as type
    FROM dates d1
    LEFT JOIN dates d2 ON ( 
        d1.StartDate > d2.StartDate
        AND 
        d1.StartDate < d2.EndDate
    )
    WHERE d2.RowId IS NULL
    GROUP BY d1.StartDate

UNION

-- find all the upper bounds
    SELECT d1.EndDate AS boundary, 'upper' as type
    FROM dates d1
    LEFT JOIN dates d2 ON ( 
        d1.EndDate > d2.StartDate
        AND 
        d1.EndDate < d2.EndDate 
    )
    WHERE d2.RowId IS NULL
    GROUP BY d1.StartDate
) as boundaries

ORDER BY boundary ASC

The result of this query on your data is

boundry    | type
------------------
2011-01-01 | lower
2011-02-20 | upper
2011-03-01 | lower
2011-04-01 | upper

The date ranges you are after are between consecutive lower and upper bounds shown abouve. With a bit of post processing, these can easily be found.