dellasavia dellasavia - 6 months ago 7
SQL Question

How can I group by a time interval inside a specific period?

Given the following structure and data:

user initial_date final_date
'JOHN' '2016-05-05 18:21:51', '2016-05-05 18:50:34'
'JOHN' '2016-05-05 18:51:44', '2016-05-05 19:10:54'


And considering the period between
2016-05-05 00:00:00
and
2016-05-05 18:59:59
, I need to find out how much minutes John spent in these registers like this:

user initial_date final_date minutes_spent
'JOHN' '2016-05-05 18:20:00', '2016-05-05 18:50:00' 30
'JOHN' '2016-05-05 18:51:00', '2016-05-05 19:10:00' 9


At the second line the result is 9 because my searching period is until 18:59:59, so must ignore the period after 19:00:00.

Answer

Does this work for you:

select timestampdiff(MINUTE, greatest(initial_date,$start_of_range),
 least(final_date,$end_of_range)) from tbl where initial_date between 
 $start_of_range and $end_of_range or 
 final_date between $start_of_range and $end_of_range

?