Problem in computation of late hours(disregard the seconds)

[newphpcoder]

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..

 

[tipsmail7]

a bit ugly solution

 

inspired from http://stackoverflow.com/questions/5764560/how-do-i-round-a-mysql-time-to-the-nearest-hour-not-a-date-time-timestamp

 

Step by step

• calculate the difference in seconds unit
  
• divide it with 60 (so we have the difference in minutes unit)
  
• FLOOR it (take it to nearest integer)
  
• add with 1
  
• multiply by 60 (to return it in seconds unit)
   
  

 

so the query will be:

SEC_TO_TIME( (
FLOOR( (
TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(Rendered) )  / 60 ) + 1
) *60
)

[newphpcoder]

Ok, I will try it

 

Thank you

[newphpcoder]

a bit ugly solution

 

inspired from http://stackoverflow.com/questions/5764560/how-do-i-round-a-mysql-time-to-the-nearest-hour-not-a-date-time-timestamp

 

Step by step

• calculate the difference in seconds unit
  
• divide it with 60 (so we have the difference in minutes unit)
  
• FLOOR it (take it to nearest integer)
  
• add with 1
  
• multiply by 60 (to return it in seconds unit)
   
  

 

so the query will be:

SEC_TO_TIME( (
FLOOR( (
TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(Rendered) )  / 60 ) + 1
) *60
)

 

I tried it:

 

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('09:35:00') AND time_to_sec('09:59:00')
AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
then SEC_TO_TIME( (FLOOR( (TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(Rendered) )  / 60 ) + 1) *60)

 

with this data:

 

login in 2012-03-01 05:37:18

 

logout 2012-03-01 13:36:35

 

the rendered is : 07:58:41

 

late: 00:01:19

 

 

[newphpcoder]

I tried your code but when the rendered  = 08:00:00 the late become 00:01:00 it should only be 00:00:00

 

Thank you

[tipsmail7]

 

I tried it:

 

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('09:35:00') AND time_to_sec('09:59:00')
AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
then SEC_TO_TIME( (FLOOR( (TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(Rendered) )  / 60 ) + 1) *60)

 

with this data:

 

login in 2012-03-01 05:37:18

 

logout 2012-03-01 13:36:35

 

the rendered is : 07:58:41

 

late: 00:01:19

hmmm, its strange

 

As far as i know your trouble lies in this code

sec_to_time(time_to_sec('08:00:00') - time_to_sec(Rendered))

 

so i suggest you to only change that line

when I try it myself

SELECT SEC_TO_TIME( (
FLOOR( (
TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(  '07:58:41' ) ) /60 ) +1
) *60
)

 

the result:

00:02:00

[newphpcoder]

Yes,

 

But I notice when no lat, because his rendered is 08:00:00, using the below code it has late 00:01:00

 

SELECT SEC_TO_TIME( (FLOOR( (TIME_TO_SEC(  '08:00:00' ) - TIME_TO_SEC(  '08:00:00' ) ) /60 ) +1) *60)

 

Thank you

[newphpcoder]

Using your suggested code I count the late correctly but when the Rendered is 08:00:00 I got problem because it add late 1

 

Thank you

[tipsmail7]

Ah my bad. Instead using FLOOR, we could use CEIL

 

SEC_TO_TIME ( CEIL (( TIME_TO_SEC ( time1) - TIME_TO_SEC ( time2) ) / 60) * 60)

 

[newphpcoder]

Thank you...

 

It works