FR STAR FR STAR - 3 months ago 9
MySQL Question

How to calculate total minutes before end of day?

Let say in my DB table I've the following records

enter image description here

Now how can I get the total minutes that student 9652 spent on 2016-08-24 00:00:01 till 2016-08-24 23:59:59 ? The query I tried

SELECT Sum(Timestampdiff(minute, start, end_date)) AS shift_mins FROM


`student_shift_history` WHERE student_id = 9652


AND `start` BETWEEN '2016-08-24 00:00:01' AND '2016-08-24 23:59:59'


Above query gives the total minutes sum of next day also.

What I need is total minutes that student 9652 spent on 2016-08-24. Any help would be appreciated.

Answer

You're considering the end_date for every row. You need to check whether the end_date is greater than the end date of the date you are checking.

I can not try it at the moment, but pseudo-working mysql could look like this:

SELECT Sum(
   Timestampdiff(minute, start, LEAST(end_date, '2016-08-24 23:59:59'))
) AS shift_mins 
FROM 
    `student_shift_history` 
WHERE  student_id = 9652 
AND `start` BETWEEN '2016-08-24 00:00:01' AND '2016-08-24 23:59:59' 

The query is supposed to say:

Take all the records where the start date is in range and then for all those, sum up the number of minutes between the start time and either the end_date or the end of the day I'm interested in if that's before end_date.


The question how to add the remaining minutes for the next day has a similar solution.

First of all, you need to make sure that start is treated in a smiliar way when calculating the minutes:

SELECT Sum(
   Timestampdiff(
       minute, 
       GREATEST(start, '2016-08-24 00:00:00'), 
       LEAST(end_date, '2016-08-24 23:59:59')
   )
) AS shift_mins 

Similar to the last change, this does the following:

  • If start is the previous day, count only from the start of the day you're interested in
  • If start is some time of the day you're interested in, everything is like it was before

Now you also need to adjust your WHERE clause to make sure that you get all kinds of records you want. These are

  • Records where both start and end_date are within the day you're interested in
  • Records where start is before the day you're interested in and end_date is after that day
  • Records where start is before the day you're interested in and end_date is within that day
  • Records where start is within the day and end_date is after

The condition for that is surprisingly simple:

WHERE
    start < `2016-08-24 23:59:59` AND end_date > `2016-08-24 00:00:00`

So the entire statement should look like this:

SELECT Sum(
   Timestampdiff(
       minute, 
       GREATEST(start, '2016-08-24 00:00:00'), 
       LEAST(end_date, '2016-08-24 23:59:59')
   )
) AS shift_mins 
FROM 
    `student_shift_history` 
WHERE  
    student_id = 9652 AND
    start < `2016-08-24 23:59:59` AND 
    end_date > `2016-08-24 00:00:00`

That should work. If it does not, you still get the picture :-)

Comments