Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    147
    Rep Power
    14

    Configuring hours worked for timesheet


    Hi, I'm setting up a timesheet system with PHP / MySQL and can't figure out how I can calculate hours worked based on a start time, end time and deducting a lunch time value. I saw another thread for this but no information about how the functions could be used.

    Example:

    Someone starts at 08:30 and finishes at 17:00, with a half-hour lunch. I have three fields for this- StartTime, LunchTime (the user enters 00:30 for half an hour) and EndTime. What I need is for the system to look at these times and work out that 8 hours have been worked, and enter this as an integer when the form has been sent (it doesn't need to go into the database, just on the summary / confirmation page for the person who's filling in the timesheets)

    Likewise, If they started a quarter of an hour later, it should return 7.75.

    That way, I can easily add up the total number of hours worked for the week, multiply by the pay rate and therefore report on the pay.

    Anyone know how this can be done? Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    You convert the times to a timestamp (strtotime) than you can do the necessary calculations arithmetically.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    147
    Rep Power
    14
    Originally Posted by gw1500se
    You convert the times to a timestamp (strtotime) than you can do the necessary calculations arithmetically.
    Would I do it like this:

    $starttime = strtotime ($starttimefromform);
    $endtime = strtotime ($endtimefromform);
    $lunchtime = strtotime ($lunchtimefromform);

    And then

    $hoursworked = $endtime - $starttime - $lunchtime

    ??

    Suspect it isn't that simple...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    It is almost that simple.
    PHP Code:
    $starttime strtotime ($starttimefromform);
    $lunchtimestart strtotime ($lunchtimestartfromform);
    $lunchtimeend strtotime ($lunchtimeendfromform);
    $endtime strtotime ($endtimefromform);
    $hoursworked $lunchtimestart $starttime $endtime $lunchtimeend 

    Comments on this post

    • Jacques1 disagrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Why do you still fumble with those Unix timestamps? The DateTime class exists since 2006.

    In MySQL, the time calculation is actually a one-liner:

    sql Code:
    SELECT
    	SUBTIME(TIMEDIFF(end_time, start_time), lunch_duration)
    FROM
    	whatever
    ;
    Last edited by Jacques1; October 18th, 2013 at 10:48 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    147
    Rep Power
    14
    Originally Posted by gw1500se
    It is almost that simple.
    PHP Code:
    $starttime strtotime ($starttimefromform);
    $lunchtimestart strtotime ($lunchtimestartfromform);
    $lunchtimeend strtotime ($lunchtimeendfromform);
    $endtime strtotime ($endtimefromform);
    $hoursworked $lunchtimestart $starttime $endtime $lunchtimeend 
    Thanks, I will give it a go and see if I can get it working...
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    Old habits die hard and as a matter of personal choice I find it easier to comprehend and program.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Except that you forgot the obligatory /3600.

    The code also won't work around midnight, because the relative times will be associatated with two different days. You need to set an absolute day like "1970-1-1" or something.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    I left it as an exercise for the student to handle rounding to seconds, minutes or hours and the format of same.

    I don't understand your second comment. The timestamp is the number of milliseconds from the epoch regardless of the day or crossover at midnight. The arithmetic calculations still result in the desired elapsed time.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by gw1500se
    The arithmetic calculations still result in the desired elapsed time.
    No. A time like "15:02" obviously doesn't specify an absolute point of time, so PHP will assume you mean today, 15:02. Unfortunately, "today" will change around midnight. If that happens, you'll get nonsense results.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    Agreed but I didn't think the form would return something that narrow. I suppose I should have included that caveat but my thought process was that the time on the form would be a check in/out only and the actual time would come from the server clock when the form was submitted. I realize now that the OP had intended the time to be an entered value.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Someone starts at 08:30 and finishes at 17:00, with a half-hour lunch. I have three fields for this- StartTime, LunchTime (the user enters 00:30 for half an hour) and EndTime. What I need is for the system to look at these times and work out that 8 hours have been worked, and enter this as an integer when the form has been sent (it doesn't need to go into the database, just on the summary / confirmation page for the person who's filling in the timesheets)
    From a "been there, done that" point of view, I would definately store the date+time that the user entered, not just the final number of hours. Not only because users must be able to review their entries on a later date, the hourly rate may also vary by time of day; overtime is more expensive and "4 hours" doesn't tell you if they started at 13:00 or 19:00.

    Also be wary of daylight savings time, if applicable.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    147
    Rep Power
    14
    I've tried the mySQL method (as the other method required me to create some new fields for start and end of lunchtimes) just to see if it might work. Unfortunately it doesn't (throws a mySQL error, and I'm wondering if this is because the fields are of type TIME).

    I'm entering the values as 08:00, 15:00 etc. which i would have thought would be correct.

    The code that tries to insert the data is as follows (I haven't finished adding the mySQL statement for calculating the time worked, but the code isn't even getting that far):

    PHP Code:
    <?php
    include ('inc/dbconnect.php');

    if(
    $_POST['createtimesheet-addtotable'])
    {
      
    $contractorid mysql_real_escape_string(@$_POST['_ID']);
      
    $surname mysql_real_escape_string(@$_POST['_Surname']);
      
    $firstname mysql_real_escape_string(@$_POST['_Firstname']);
      
    $clientname mysql_real_escape_string(@$_POST['_ClientName']);
      
    $weekcommencing mysql_real_escape_string(@$_POST['_WeekCommencing']);
      
      
    $mondayrate mysql_real_escape_string(@$_POST['_MondayRate']);
      
    $mondaystarttime mysql_real_escape_string(@$_POST['_MondayStartTime']);
      
    $mondayendtime mysql_real_escape_string(@$_POST['_MondayEndTime']);
      
    $mondaylunchstart mysql_real_escape_string(@$_POST['_MondayLunchStart']);
      
    $mondaylunchend mysql_real_escape_string(@$_POST['_MondayLunchEnd']);
      
      
    $tuesdayrate mysql_real_escape_string(@$_POST['_TuesdayRate']);
      
    $tuesdaystarttime mysql_real_escape_string(@$_POST['_TuesdayStartTime']);
      
    $tuesdayendtime mysql_real_escape_string(@$_POST['_TuesdayEndTime']);
      
    $tuesdaylunchstart mysql_real_escape_string(@$_POST['_TuesdayLunchStart']);
      
    $tuesdaylunchend mysql_real_escape_string(@$_POST['_TuesdayLunchEnd']);
      
      
    $wednesdayrate mysql_real_escape_string(@$_POST['_WednesdayRate']);
      
    $wednesdaystarttime mysql_real_escape_string(@$_POST['_WednesdayStartTime']);
      
    $wednesdayendtime mysql_real_escape_string(@$_POST['_WednesdayEndTime']);
      
    $wednesdaylunchstart mysql_real_escape_string(@$_POST['_WednesdayLunchStart']);
      
    $wednesdaylunchend mysql_real_escape_string(@$_POST['_WednesdayLunchEnd']);
      
      
    $thursdayrate mysql_real_escape_string(@$_POST['_ThursdayRate']);
      
    $thursdaystarttime mysql_real_escape_string(@$_POST['_ThursdayStartTime']);
      
    $thursdayendtime mysql_real_escape_string(@$_POST['_ThursdayEndTime']);
      
    $thursdaylunchstart mysql_real_escape_string(@$_POST['_ThursdayLunchStart']);
      
    $thursdaylunchend mysql_real_escape_string(@$_POST['_ThursdayLunchEnd']);
      
      
    $fridayrate mysql_real_escape_string(@$_POST['_FridayRate']);
      
    $fridaystarttime mysql_real_escape_string(@$_POST['_FridayStartTime']);
      
    $fridayendtime mysql_real_escape_string(@$_POST['_FridayEndTime']);
      
    $fridaylunchstart mysql_real_escape_string(@$_POST['_FridayLunchStart']);
      
    $fridaylunchend mysql_real_escape_string(@$_POST['_FridayLunchEnd']);
      
      
    $saturdayrate mysql_real_escape_string(@$_POST['_SaturdayRate']);
      
    $saturdaystarttime mysql_real_escape_string(@$_POST['_SaturdayStartTime']);
      
    $saturdayendtime mysql_real_escape_string(@$_POST['_SaturdayEndTime']);
      
    $saturdaylunchstart mysql_real_escape_string(@$_POST['_SaturdayLunchStart']);
      
    $saturdaylunchend mysql_real_escape_string(@$_POST['_SaturdayLunchEnd']);
      
      
    $sundayrate mysql_real_escape_string(@$_POST['_SundayRate']);
      
    $sundaystarttime mysql_real_escape_string(@$_POST['_SundayStartTime']);
      
    $sundayendtime mysql_real_escape_string(@$_POST['_SundayEndTime']);
      
    $sundaylunchstart mysql_real_escape_string(@$_POST['_SundayLunchStart']);
      
    $sundaylunchend mysql_real_escape_string(@$_POST['_SundayLunchEnd']);
      
      
    $dateadded date("Y-m-d");
      
    $addedby mysql_real_escape_string(@$_POST['_AddedBy']);

    // Build SQL Query  
    $query "INSERT INTO timesheets (ContractorID, Surname, Firstname, ClientName, WeekCommencing, MondayRate, MondayStartTime, MondayEndTime, MondayLunchStart, MondayLunchEnd, TuesdayRate, TuesdayStartTime, TuesdayEndTime, TuesdayLunchStart, TuesdayLunchEnd, WednesdayRate, WednesdayStartTime, WednesdayEndTime, WednesdayLunchStart, WednesdayLunchEnd, ThursdayRate, ThursdayStartTime, ThursdayEndTime, ThursdayLunchStart, ThursdayLunchEnd, FridayRate, FridayStartTime, FridayEndTime, FridayLunchStart, FridayLunchEnd, SaturdayRate, SaturdayStartTime, SaturdayEndTime, SaturdayLunchStart, SaturdayLunchEnd, SundayRate, SundayStartTime, SundayEndTime, SundayLunchStart, SundayLunchEnd, DateAdded, AddedBy) VALUES ('$contractorid','$surname','$firstname','$clientname','$weekcommencing','$mondayrate','$mondaystarttime','$mondayendtime','$mondaylunchstart','$mondaylunchend','$tuesdayrate','$tuesdaystarttime','$tuesdayendtime','$tuesdaylunchstart','$tuesdaylunchend','$wednesdayrate','$wednesdaystarttime','$wednesdayendtime','$wednesdaylunchstart','$wednesdaylunchend','$thursdayrate','$thursdaystarttime','$thursdayendtime','$thursdaylunchstart','$thursdaylunchend','$fridayrate','$fridaystarttime''$fridayendtime','$fridaylunchstart','$fridaylunchend','$saturdayrate','$saturdaystarttime','$saturdayendtime','$saturdaylunchstart','$saturdaylunchend','$sundayrate','$sundaystarttime','$sundayendtime','$sundaylunchstart','$sundaylunchend','$dateadded','$addedby')"// specify the table and field names for the SQL query

    echo $query;

    if(
    $result mysql_query($query)) 
    {     
    $gethours 'SELECT SUBTIME(TIMEDIFF(MondayEndTime, MondayStartTime), MondayLunch) FROM timesheets';

    $result2 mysql_query($gethours);
    echo 
    $result2;

        
    header('location: admin-confirmed.html');
    } else {
         echo 
    "ERROR: Data not added";
    }
    }

    ?>
    Last edited by galahad3; October 21st, 2013 at 09:18 AM.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,392
    Rep Power
    594
    Please enclose your code in [ PHP ] tags. See the sticky at the top of this forum that says READ THIS BEFORE POSTING. Also you need to get rid of the mysql_real_escape_string junk and the '@' everywhere and stop using the deprecated MySQL extensions. If you are going to do this, do it right from the start and use PDO with prepared statements. Finally, if you have defined the fields in the database as time, then you need to store time values in it. However, since you did not post what the actual error is, one can only guess what might be the problem.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    147
    Rep Power
    14
    Originally Posted by gw1500se
    Please enclose your code in [ PHP ] tags. See the sticky at the top of this forum that says READ THIS BEFORE POSTING. Also you need to get rid of the mysql_real_escape_string junk and the '@' everywhere and stop using the deprecated MySQL extensions. If you are going to do this, do it right from the start and use PDO with prepared statements. Finally, if you have defined the fields in the database as time, then you need to store time values in it. However, since you did not post what the actual error is, one can only guess what might be the problem.
    I wasn't aware mysql_real_escape_string was deprecated- I had read that it was useful to include it as a security measure when inserting data into mySQL, which is why I used it.

    The error was simply the "Data not added" which is what happens if the query doesn't work.

    I'm aware I have to input times into the time fields, and I thought using 08:00, 09:00 etc. would be correct. If it isn't, what format should the times be in?
    Last edited by galahad3; October 21st, 2013 at 09:19 AM.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo