SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If the rest of your code is in PHP, you could just run it through a PHP function

    PHP Code:
    $rounded date('H:i:s'round(strtotime('16:45:34')/60)*60); // will give 16:46:00 
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THank you


Bookmarks

Posting Permissions

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