mysql - sum interval dates

Question

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

Answer 1

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;

Answer 2

Consider generating a table of hours as timestamps:

HOUR
----
2014年10月22日 12:00
2014年10月22日 13:00
2014年10月22日 14:00
2014年10月22日 15:00
...

Then you can select the hours that are between the periods of your original table.

Something like the following:

select name, count(distinct hour) 
from HOUR join PERIOD 
where hour between PERIOD.start and PERIOD.end
group by name