FR STAR - 4 years ago 127
MySQL Question

# How to calculate total minutes before end of day?

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

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.

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 :-)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download