Mysql – sum interval dates

MySQL

I would like to sum the hours for each name, giving a total interval between START and END activities. It would be simple if I could subtract from each record the end of the beginning. For example, Mary started at 13:00 and worked up to 15:00 and started another activity from 14:00 to 16:00. I would like the result to be 3 (she used 3 hours of their time to perform both activities).

Example data

Name    |    START               |    END                 |
-----------------------------------------------------------
KATE    | 2014-01-01 13:00:00    | 2014-01-01 14:00:00    |
MARY    | 2014-01-01 13:00:00    | 2014-01-01 15:00:00    |
TOM     | 2014-01-01 13:00:00    | 2014-01-01 16:00:00    |
KATE    | 2014-01-01 12:00:00    | 2014-01-02 04:00:00    |
MARY    | 2014-01-01 14:00:00    | 2014-01-01 16:00:00    |
TOM     | 2014-01-01 12:00:00    | 2014-01-01 18:00:00    |
TOM     | 2014-01-01 22:00:00    | 2014-01-02 02:00:00    |

Result

KATE    15 hours
MARY     3 hours
TOM      9 hours

Best Answer

You could merge overlapping intervals and count the hours from there (untested):

select name, min(start), end 
from (
    select x.name, x.start, min(y.end) as end 
    from t as x 
    join t as y 
        on x.name = y.name 
       and x.start <= y.end 
       and not exists (
           select 1 
           from t as z 
           where y.name = z.name 
             and y.end >= z.start 
             and y.end < z.end
       ) 
    where not exists (
        select 1 
        from t as u 
        where x.name = u.name 
          and x.start > u.start 
          and x.start <= u.start
    ) 
    group by x.name, x.start
) as v group by name, end;
Related Question