Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Problem in computation of late hours(disregard the seconds)

    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:

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

    Code:
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since this produces the difference in seconds --
    Code:
    time_to_sec('08:00:00') - time_to_sec(Rendered)
    all you have to do is round it upwards to next multiple of 60 before applying sec_to_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    since this produces the difference in seconds --
    Code:
    time_to_sec('08:00:00') - time_to_sec(Rendered)
    all you have to do is round it upwards to next multiple of 60 before applying sec_to_time
    What do you mean round it upwards to next multiple of 60 before applying sec_to_time?

    Thank you.

    Can you sight an example

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, suppose the difference is 534 seconds

    divide by 60 using integer arithmetic (which discards fractions)

    answer: 8

    add 1 and multiply by 60

    answer: 540

    apply sec_to_time and you get 9:00
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    okay, suppose the difference is 534 seconds

    divide by 60 using integer arithmetic (which discards fractions)

    answer: 8

    add 1 and multiply by 60

    answer: 540

    apply sec_to_time and you get 9:00
    You mean this:

    PHP Code:
    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 60 (time_to_sec('08:00:00') - time_to_sec(Rendered))
    END
    or
    PHP Code:
    time_to_sec'08:00:00') + (60 MODtime_to_secRendered60 ) ) 
    Thank you

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happened when you tested those?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    I tried this code:

    Code:
    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)
    but when the rendered = 08:00:00 the late become 00:01:00 it should only be 00:00:00

    Thank you
    Last edited by newphpcoder; 04-18-12 at 04:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •