Count days in several time ranges

Question

I need to count the number of days that fall completely or partially within a set of time ranges.

The ranges do not overlap but there may be many in the same day.

For instance, given:

    +---------------------+---------------------+
    | time_start          | time_end            |   
    +---------------------+---------------------+
    | 2009-12-10 23:59:00 | 2009-12-11 01:38:00 |
    | 2009-12-13 15:40:00 | 2009-12-13 15:45:00 |
    | 2009-12-13 15:45:00 | 2009-12-13 15:50:00 |
    | 2009-12-13 15:53:00 | 2009-12-13 15:56:00 |
    | 2009-12-14 17:08:00 | 2009-12-14 17:12:00 |
    | 2009-12-16 07:23:00 | 2009-12-18 14:32:00 |
    +---------------------+---------------------+

The result should be 7 (days 10, 11, 13, 14, 16, 17 and 18).

Any hint on how to achieve this in mysql will be appreciated.

Answer 1

I'm not a MYSQL dev but in MSSQL I would

• Build a dates table (a table that has a record for every date between say 1900 and 2100, at least enough to easily cover the range you're looking at).
• Do a join from your table above to your dates table where dates_table.date between time_start and time_end
• Then count the distinct number of days

The key to this though is the dates table.

Hope this gets you started.