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

我将此表命名为 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       |
+----+--------+---------------------+---------+

其中eventid 1 = Start work和eventid 2 = Stop work。

考虑工作时间是所有eventid的1和2之间的总小时数，我如何计算给定日期的小时数-WHERE emplid = 13 AND Year(ctimestamp) = 2016 and Month(ctimestamp) = 06 and Day(ctimestamp) = 02

最佳答案

您也可以使用PHP(而不是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.
?>

您可以舍入结果。

将先前的代码复制粘贴到文件中，另存为.PHP并在浏览器中打开它。随时更改样本数据。

编辑:更容易调用函数来计算所有时间:

<?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.
?>

使用从查询中获得的sql-result作为参数调用此函数(并将foreach替换为while ( $row = mysqli_fetch_array)。