MySQL - PHP: Calculate total hours in a day between multiple events

Question

I have this table named time_track:

+----+--------+---------------------+---------+
| id | emplid | ctimestamp          | eventid |
+----+--------+---------------------+---------+
| 1  | 13     | 2016-06-02 03:41:41 | 1       |
+----+--------+---------------------+---------+
| 2  | 13     | 2016-06-02 09:04:49 | 2       |
+----+--------+---------------------+---------+
| 3  | 13     | 2016-06-02 10:03:13 | 1       |
+----+--------+---------------------+---------+
| 4  | 13     | 2016-06-02 13:21:23 | 2       |
+----+--------+---------------------+---------+

where eventid 1 = Start work and eventid 2 = Stop work.

How can I calculate the hours of any given day taking into consideration that working hours are the total hours between all eventid's 1 and 2 - WHERE emplid = 13 AND Year(ctimestamp) = 2016 and Month(ctimestamp) = 06 and Day(ctimestamp) = 02

Answer 1

You can also do it with PHP (instead of SQL) :

<?php
$data = array( array( "1","2016-06-02 03:41:41" ),
               array( "2","2016-06-02 09:04:49" ),
               array( "1","2016-06-02 10:03:13" ),
               array( "2","2016-06-02 13:21:23" )
             );
$hours = 0;
foreach ( $data as $row ) // PROCESS ALL ROWS FROM QUERY.
{ if ( $row[ 0 ] == "1" ) // IF CURRENT ROW IS START TIME
       $start = strtotime( $row[ 1 ] );
  else { $stop = strtotime( $row[ 1 ] ); // STOP TIME. CALCULATE.
         $hours += ( $stop - $start ) / 3600;
       }
}
echo $hours; // 8.6883333333333.
?>

You can round the result.

Copy-paste previous code in a file, save it as .PHP and open it in your browser. Feel free to change the sample data.

Edit : it's easier to call a function to calculate all the hours :

<?php

function total_hours ( $data )
{ $hours = 0;
  foreach ( $data as $row )
    if ( $row[ "eventid" ] == "1" )
         $start = strtotime( $row[ "ctimestamp" ] );
    else { $stop = strtotime( $row[ "ctimestamp" ] );
           $hours += ( $stop - $start ) / 3600;
         }
  return $hours;
}

$sample_data = array( array( "id"         => 1,
                             "emplid"     => 13,
                             "ctimestamp" => "2016-06-02 03:41:41",
                             "eventid"    => 1 ),
                      array( "id"         => 2,
                             "emplid"     => 13,
                             "ctimestamp" => "2016-06-02 09:04:49",
                             "eventid"    => 2 ),
                      array( "id"         => 3,
                             "emplid"     => 13,
                             "ctimestamp" => "2016-06-02 10:03:13",
                             "eventid"    => 1 ),
                      array( "id"         => 4,
                             "emplid"     => 13,
                             "ctimestamp" => "2016-06-02 13:21:23",
                             "eventid"    => 2 )
                    );
echo total_hours( $sample_data ); // 8.6883333333333.
?>

Call this function with the sql-result you get from the query as parameter (and replace the foreach by while ( $row = mysqli_fetch_array ).

Answer 2

Unlike SQL only answers below, it seams efficient to combine SQL and PHP for readability and performance. Moreover, PHP will allow you to code how to handle exceptions like missing or double data.

<?php
// Generate test data like it would be returned by a PDO::fetchAll(PDO:::FETCH_ASSOC) with
// SELECT * FROM time_track 
//    WHERE emplid = 13 AND Year(ctimestamp) = 2016 and Month(ctimestamp) = 06 and Day(ctimestamp) = 02 
//    ORDER BY ctimestamp
$logs[] = ['ctimestamp'=>'2016-06-02 03:41:41', 'eventid'=>1];
$logs[] = ['ctimestamp'=>'2016-06-02 09:04:49', 'eventid'=>2];
$logs[] = ['ctimestamp'=>'2016-06-02 10:03:13', 'eventid'=>1];
$logs[] = ['ctimestamp'=>'2016-06-02 13:21:23', 'eventid'=>2];

// Compute working time
$worktime = 0; // in seconds
foreach ($logs as $log) {
    if ($log['eventid'] == 1) {  // start work
        $startWork = $log['ctimestamp'];
    } else { // end work
        $endWork = $log['ctimestamp'];
        $worktime += strtotime($endWork) - strtotime($startWork);
    }
}
echo gmdate('H:i', $worktime); // seconds to hours:minutes
?>

Running code: http://phpfiddle.org/main/code/jx8h-ztuy

Below is a tested refinement of the above code including database access and a loop.
Since you indicate performance is crucial, you may want to use PDO::prepare()

<pre>
<?php
class TimeLog {
    private $pdo;
    private $pdoStatement;

    // constructor: connect to database and prepare statement
    function __construct(){
        // adapt following constructor to your database configuartion
        $this->pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'username', 'password'); 
        $this->pdoStatement = $this->pdo->prepare(
            'SELECT * FROM time_track 
                WHERE emplid = :emplid 
                AND DATE(ctimestamp) = :cdatestamp
                ORDER BY ctimestamp
        ');
    }

    // compute workTime for given employee and date
    function workTime($emplid, $cdatestamp) {
        // fetch from database, executing prepared statement
        $this->pdoStatement->execute([':emplid'=>$emplid, ':cdatestamp'=>$cdatestamp]);
        $logs = $this->pdoStatement->fetchAll(PDO::FETCH_ASSOC);

        // compute working time
        $worktime = 0; // in seconds
        foreach ($logs as $log) {
            if ($log['eventid'] == 1) {  // start work
            $startWork = $log['ctimestamp'];
            } else { // end work
                $endWork = $log['ctimestamp'];
                $worktime += strtotime($endWork) - strtotime($startWork);
            }
        }
        return gmdate('H:i', $worktime); // convert seconds to hours:minutes
    }
}

$timeLog = new Timelog(); // invoke constructor once

$emplid = 13; // example

// echo registration of last seven days
for ($date = strtotime('-7 days'); $date <= time(); $date += 24 * 3600) {
    // convert $date to YYYY-MM-DD format
    $cdatestamp = date('Y-m-d', $date); 
    // execute one SQL statement and return computed worktime
    $workTime = $timeLog->workTime($emplid, $cdatestamp); 
    // show result
    echo $cdatestamp, "\t", $workTime, "\n";
}
?>

Answer 3

You have to self-join your table on itself in a subuery and get the min of eventid=2 that is greater than the eventid=1 and calculate the difference between these 2 records. In the outer query you sum up the differences by day of the eventid=1 timestamps:

select date(t3.ctimestamp), t3.emplid, sum(diff) / 60 as hours_worked
from
    (select t1.id, t1.ctimestamp, t1.emplid, min(t2.ctimestamp) as mintime, timestampdiff(minute,min(t2.ctimestamp), t1.ctimestamp) as diff 
     from yourtable t1
     inner join yourtable t2 on t1.ctimestamp<t2.ctimestamp
     where t1.eventid=1
           and t2.eventid=2
           and t1.emplid=13
           and t2.emplid=13
           and date(t1.ctimestamp)='2016-06-02' --may have checked out the next day, I do not know how you want to handle that
     group by t1.id, t1.ctimestamp, t1.emplid) t3
group by date(t3.ctimestamp)

In a live environment I would not base a solution on the id column having no gaps, even if it is an auto increment column. Usually there are gaps. You also need to decide what happens if you have orphan check in or check out events. My code assumes that each check in has a corresponding check out.

Answer 4

You can try something like this

You can group by year and month as well if you need to further separate your data.

select day(ctimestamp) as Day, hour(ctimestamp) as Hour, count(*) as Count
from MyTable
where ctimestamp between :date1 and :date2
group by day(ctimestamp), hour(ctimestamp)

Answer 5

You will need to calculate difference between the timestamps for events of 1 and events of 2. This does assume that for any given day, there are only 2 events per empl_id and the the checkout time is within the same day (e.g., overnight hours will not show in this query). It is not a very robust solution, but I am unsure of the integrity of your data and the edge cases you need to handle.

SELECT TIMEDIFF(t1.c_timestamp, t2.c_timestamp) / 3600 AS hourDiff
FROM time_track t1
INNER JOIN time_track t2 
ON (t2.id > t1.id AND t2.event_id = 2 AND t1.empl_id = t2.empl_id AND DAY(t2.c_timestamp) = DAY(t1.c_timestamp) AND MONTH(t2.c_timestamp) = MONTH(t1.c_timestamp) AND YEAR(t2.c_timestamp) = YEAR(t1.c_timestamp))
WHERE t1.event_id = 1 AND t1.empl_id = 13 AND Year(t1.c_timestamp) = 2016 and Month(t1.c_timestamp) = 6 and Day(t1.c_timestamp) = 2

Answer 6

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55') - UNIX_TIMESTAMP('2010-11-29 13:13:55') as output

$entry_time = [... get it here using query...]

$exit_time = [...get it here using query...]

$query = " SELECT UNIX_TIMESTAMP(' ".$entry_time." ') - UNIX_TIMESTAMP(' ".$exit_time." ') as days;";

Answer 7

QUERY :SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;

$time_entry = [...] // find query and save out put to this variable

$time exit = [...] // find query and save out put to this variable

$query = "SELECT DATEDIFF('". time_entry ."', '".time_exit."') AS days;"