Get all dates from MySQL in the passed week and work out each days hours

Question

I am currently starting a punchclock for our work. This will log employees, In, Lunch Out, Lunch In and Out each day.

I then would like to calculate times each week but specify the week too. So in MySQL lookup all dates in the selected week and then each day calculate the time between IN and OUT minus the lunch.

I can't get my head around how best to do this.

The table looks like:

id | user_id | punch_time | punch_status

The punch status' are:

In = 1
Out = 0
Lunch = 2

This is what i have already which is showing all the results in the past week.

   function getTotalHoursThisWeek($week)
   {
    include("db.php");

    $currUser = getUser('id');
    if($week == "CURRENT")
    {
         $query = "SELECT * FROM punch_clock WHERE YEARWEEK(`punch_time`, 1) = YEARWEEK(CURDATE(), 1) && user_id = $currUser";
     } else {
         $query = "SELECT * FROM punch_clock WHERE YEARWEEK(`punch_time`, 1) = YEARWEEK($week, 1) && user_id = $currUser";
    }

    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result))
    {
         echo $row['punch_time']. "<br />";
    }


 }

This outputs the following:

2015-04-29 17:04:00 - 0
2015-04-29 08:38:03 - 1
2015-04-30 17:20:00 - 0
2015-04-30 09:19:23 - 1
2015-05-01 09:09:47 - 1
2015-05-01 12:36:44 - 2
2015-05-01 12:57:58 - 1

However i would like to then take each day and then calculate the time from In(1) until Out(1) including any entry for Lunch out(2) and then back In(1) for that day.

After that, add them all together to get the total time for that week.

can you come some example rows that you will have, will it be an in, lunch, out row? or in, lunch, in, out row? — Jonathan, May 01 '15 at 13:38
@Augwa - Added for you. Let me know if you would like anything else. — tutchmedia, May 01 '15 at 13:48

Jonathan · Accepted Answer · 2015-05-05T12:52:08.050

This should put you on the right track.

function getTotalHoursThisWeek($week)
{
    /**
    * You should consider moving this elsewhere as it seems you are creating
    * a database connection every time you hit this function.
    */ 
    include("db.php");

    $userId = getUser('id');

    /**
     * removed the if statement and combined into a single query, easier to maintain
     */
    $query = sprintf("SELECT * FROM punch_clock pc WHERE YEARWEEK(pc.punch_time, 1) = YEARWEEK(%s, 1) AND pc.user_id = %d ORDER BY pc.punch_time ASC", $week == "CURRENT" ? "CURDATE()" : "'$week'", $userId);

    /**
    * consider looking into using PDO or MySQLi instead of the mysql_* functions are they are
    * deprecated and should no longer be used
    */
    $result = mysql_query($query) or die(mysql_error());

    /** time logged in seconds **/
    $timeLogged = 0;
    $startTime = 0;
    $endTime = 0;
    while($row = mysql_fetch_array($result))
    {
        if ($row['punch_status'] == 1) {
            $startTime = strtotime($row['punch_time']);
        }

        if ($row['punch_status'] != 1) {
            $endTime = strtotime($row['punch_time']);

            $timeLogged += ($endTime - $startTime);

            $endTime = null;
            $startTime = null;
        }
    }

    /**
     * Include the final punch in, if there was no punch out
     */
    if ($startTime != null) {
        $timeLogged += (time() - $startTime);
        $startTime = null;
    }

    echo sprintf('User %d has logged %d seconds of work', $userId, $timeLogged);
}

Thankyou for this! Does the job! Is there anyway to amend it so that if it's the current day and it has no sign out, it will get the current time and count backwards? (So the user can see there hours in realtime today only) — tutchmedia, May 05 '15 at 08:08

score 0 · Answer 2 · edited May 23 '17 at 12:06

There's probably not a great way to do this in MySQL only, because you would need to JOIN the table back to itself, and that JOIN would be extremely complicated.

Instead, I would recommend doing this in PHP. You can do that by keeping a running tab in your result loop:

$intSec = 0; // instantiate the total
$strTimeIn = ''; // This will keep track of the last time a user clocked in

while($row = mysql_fetch_array($result))
{
  // Determine if you're in or out
  if ($row['punch_status'] === '1') { // you're in -- store this information
    $strTimeIn = $row['punch_time'];
  } else { // you're clocking out -- add the difference in time to our aggregator
    $intSec = $intSec + (strtotime($row['punch_time']) - strtotime($strTimeIn));
}

echo "User has worked for $intSec seconds this week!";

You can convert the number of seconds into hours / minutes / seconds:

gmdate("H:i:s", $intSec);

Or just hours:

$intSec / 3600;

Whatever makes the most sense for your application.

There's a couple of caveats to this approach:

Assumes users clock in, then clock out -- if a user forgets to clock out, this will break
Assumes entires are in order -- add an ORDER BY to your MySQL statement (ORDER BY punch_time)

Get all dates from MySQL in the passed week and work out each days hours

2 Answers2