Problem in computation of late hours(disregard the seconds)

Databases
1

Hi…

I got problem in getting the time difference between two datetimes.

My problem is how can I only get the difference of hours and minutes disregard the seconds from datetime…

here is my sample login and logout:

Login : 2012-03-12 05:39:17
Logout: 2012-03-12 13:35:16

And here is my code in getting the Rendered or total hours:


UPDATE reg_att SET Rendered = case
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00')
AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:59'))))
End;

the output of this is :
Rendered = 07:56:42

and I need to compute the lates:

I used this code for lates:


UPDATE reg_att SET Late = case
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00')
AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
then sec_to_time(time_to_sec('08:00:00') - time_to_sec(Rendered))
END;

the output of this is:

Late = 00:03:18

Now, How can be the late become : 00:04:00

Disregard the seconds?

his schedule is 05:35 - 13:35

he login 05:39, so he was late 4 minutes, but in my code it computes 00:03:18 because it regards the seconds.

Any help is highly appreciated.

Thank you so much…

2

If the rest of your code is in PHP, you could just run it through a PHP function


$rounded = date('H:i:s', round(strtotime('16:45:34')/60)*60); // will give 16:46:00
3

THank you