jQuerybeast - 1 year ago 98
PHP Question

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

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`

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` ).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download