PDA

View Full Version : Problem in computation of late hours(disregard the seconds)



newphpbees
17 Apr 2012, 08:11 PM
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..