0

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.

deldev
  • 1,296
  • 18
  • 27

1 Answers1

2

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

?

Sasha Pachev
  • 5,162
  • 3
  • 20
  • 20
  • 1
    Does this need an `OR final_date between $start_of_range and $end_of_range`? – Uueerdo Jun 10 '16 at 17:21
  • @Uueerdo, you are right! So, the records that started after 18:00:00 but finished after will be summed too. – deldev Jun 10 '16 at 17:49
  • 1
    @dellasavia oh, wait, I've dealt with this kind of problem before; even now we are omitting intervals that start before the range and end after it. – Uueerdo Jun 10 '16 at 17:58
  • I _think_ this is the smallest conditions that work `initial <= end AND final >= start` – Uueerdo Jun 10 '16 at 18:02