How to calculate total hours and minutes in a week of an employee

Question

I' am Querying to get the date and time of a employee from the database. Then I' am calculating the total hours and minutes they work in a day. Currently I' am stuck at a point where I need to calculate the total hours an employee has worked in a week. Please help, how do I calculate the total hours and minutes an employee has worked in a week.

Code

<?php
    $sql = "SELECT * FROM records WHERE records_status = 'finished' AND record_created > DATE_SUB('2014-10-19', INTERVAL 7 DAY)";
    $query = $db->SELECT($sql);
?>
<table width="39%" border="1">
  <tbody>
    <tr>
      <td style="padding: 8px;"><strong>Day</strong></td>
      <td style="padding: 8px;"><strong>Total Hours</strong></td>
    </tr>
    <?php
            $tally = "";
            foreach( $db->FETCH_OBJECT() as $row ){
                $record_sign_in = $row->record_sign_in;
                $record_sign_out = $row->record_sign_out;
                $record_created = $row->record_created;
                $time1 = date("H:i", strtotime($record_sign_in) );
                $time2 = date("H:i", strtotime($record_sign_out) );
                $record_created = date("l", strtotime($record_created) );
                $day = $record_created;
                list($hours, $minutes) = explode(':', $time1);
                    $startTimestamp = mktime($hours, $minutes);
                list($hours, $minutes) = explode(':', $time2);
                    $endTimestamp = mktime($hours, $minutes);
                $seconds = $endTimestamp - $startTimestamp;
                $minutes = ($seconds / 60) % 60;
                $hours = floor($seconds / (60 * 60));

                $tally = "What to do here?";
        ?>
    <tr>
      <td style="padding: 8px;"><?php echo $day; ?></td>
      <td style="padding: 8px;"><?php echo $hours; ?> hrs <?php echo $minutes; ?> min</td>
    </tr>
    <?php } ?>
    <tr>
      <td style="padding: 8px;">Total</td>
      <td style="padding: 8px;"></td>
    </tr>
  </tbody>
</table>

This is how the visual table looks like.

Answer 1

Use the following sequence:

1. Convert times to seconds (epoch), using strtotime.
2. Do the math
3. Convert to human format, using date or gmdate

No need for the intermediate variables, list, mktime and explodes.

Working original: https://eval.in/207141

Solution: https://eval.in/207145

<pre>
<?php
// test vector
$dbs[] = ['record_created'=>'20141201', 'record_sign_in'=>'09:30', 'record_sign_out'=>'18:30'];
$dbs[] = ['record_created'=>'20141202', 'record_sign_in'=>'09:30', 'record_sign_out'=>'18:30'];
$dbs[] = ['record_created'=>'20141203', 'record_sign_in'=>'09:25', 'record_sign_out'=>'18:30'];
$dbs[] = ['record_created'=>'20141204', 'record_sign_in'=>'09:35', 'record_sign_out'=>'18:30'];
$dbs[] = ['record_created'=>'20141205', 'record_sign_in'=>'09:50', 'record_sign_out'=>'18:30'];
$dbs = json_decode (json_encode ($dbs), FALSE); // convert to object for compatibility with code below
?>
<table width="39%" border="1">
  <tbody>
    <tr>
      <td style="padding: 8px;"><strong>Day</strong></td>
      <td style="padding: 8px;"><strong>Total Hours</strong></td>
    </tr>
    <?php
            $tally = 0; // total time
            foreach( $dbs as $row ){ // statement slightly modified for test purpose
                // convert to seconds
                $created = strtotime($row->record_created);
                // conversion and math
                $seconds = strtotime($row->record_sign_out) - strtotime($row->record_sign_in); // time difference in seconds
                $tally += $seconds;
                // human format
                $day = date("l", $created);
                $hours = gmdate("H", $seconds);
                $minutes = gmdate("i", $seconds);
        ?>
    <tr>
      <td style="padding: 8px;"><?php echo $day; ?></td>
      <td style="padding: 8px;"><?php echo $hours; ?> hrs <?php echo $minutes; ?> min</td>
    </tr>
    <?php } 
        // human format
        $hours = floor($tally / 3600);
        $minutes = gmdate("i", $tally);
    ?>
    <tr>
      <td style="padding: 8px;">Total</td>
      <td style="padding: 8px;"><?php echo $hours; ?> hrs <?php echo $minutes; ?> min</td>
    </tr>
  </tbody>
</table>

Answer 2

Well you could start by removing lots of unnecessary intermediate variable creation and then all you need to do is add the $seconds to the $tally each time through the loop and then convert $tally to minutes and seconds like you already where for each day.

<?php
    $sql = "SELECT * 
            FROM records 
            WHERE records_status = 'finished' 
              AND record_created > DATE_SUB('2014-10-19', INTERVAL 7 DAY)";
    $query = $db->SELECT($sql);
?>
<table width="39%" border="1">
  <tbody>
    <tr>
      <td style="padding: 8px;"><strong>Day</strong></td>
      <td style="padding: 8px;"><strong>Total Hours</strong></td>
    </tr>
    <?php
            $tally = 0;
            foreach( $db->FETCH_OBJECT() as $row ){

                $time1 = date("H:i", strtotime($row->record_sign_in) );
                $time2 = date("H:i", strtotime($row->record_sign_out;) );
                $day = date("l", strtotime($row->record_created) );

                list($hours, $minutes) = explode(':', $time1);
                    $startTimestamp = mktime($hours, $minutes);
                list($hours, $minutes) = explode(':', $time2);
                    $endTimestamp = mktime($hours, $minutes);

                $seconds = $endTimestamp - $startTimestamp;
                $minutes = ($seconds / 60) % 60;
                $hours = floor($seconds / (60 * 60));

                $tally += $seconds;
        ?>
    <tr>
      <td style="padding: 8px;"><?php echo $day; ?></td>
      <td style="padding: 8px;"><?php echo $hours; ?> hrs <?php echo $minutes; ?> min</td>
    </tr>
    <?php } ?>
    <tr>
      <td style="padding: 8px;">Total</td>
<?php
    $minutes = ($tally / 60) % 60;
    $hours = floor($tally / (60 * 60));
?>
      <td style="padding: 8px;">
         <?php echo $hours; ?> hrs <?php echo $minutes; ?> min
      </td>
    </tr>
  </tbody>
</table>