PHP Complex Time Sum

Question

I am trying to code a script that takes database entries like these:

╔════╦═════════╦════════╦══════════╗
║ id ║ user_id ║ action ║   time   ║
╠════╬═════════╬════════╬══════════╣
║  1 ║       1 ║      1 ║ 12:00:00 ║
║  2 ║       1 ║      2 ║ 12:10:00 ║
║  3 ║       1 ║      1 ║ 18:00:00 ║
║  4 ║       1 ║      2 ║ 18:10:00 ║
╚════╩═════════╩════════╩══════════╝

Then works out the difference between the two actions 1 & 2 and then sum them together, this example would make a total of 20:00

I don't really know where to start for this and can't seem to find anything on the net that helps.

If someone could point me in the right direction, or any help will be greatly appreciated.

Edit: Other actions may be in the database (1-7), as well as multiple users.

Edit2: More complex example table beneath, still should result in the same example of 20:00

╔════╦═════════╦════════╦══════════╗
║ id ║ user_id ║ action ║   time   ║
╠════╬═════════╬════════╬══════════╣
║  1 ║       1 ║      1 ║ 12:00:00 ║
║  2 ║       1 ║      2 ║ 12:10:00 ║
║  3 ║       2 ║      1 ║ 12:30:00 ║
║  4 ║       2 ║      2 ║ 12:40:00 ║
║  5 ║       2 ║      3 ║ 12:50:00 ║
║  6 ║       2 ║      4 ║ 13:00:00 ║
║  7 ║       3 ║      1 ║ 14:00:00 ║
║  8 ║       3 ║      2 ║ 14:10:00 ║
║  9 ║       1 ║      1 ║ 18:00:00 ║
║ 10 ║       1 ║      2 ║ 18:10:00 ║
╚════╩═════════╩════════╩══════════╝

Answer 1

After discussion SQL-FIDDLE http://sqlfiddle.com/#!2/8e5ab/5

--

SQL-FIDDLE http://sqlfiddle.com/#!2/ab988/1

SELECT user_id, SUM(time_difference) as time_spent
FROM (    
  SELECT t1.user_id,
  CASE WHEN t1.user_id = t2.user_id 
  THEN time_to_sec(t2.time_duration) - time_to_sec(t1.time_duration) ELSE 0 END time_difference
  FROM T t1 LEFT OUTER JOIN T t2
  ON t1.id = t2.id - 1
  JOIN (SELECT @DIS := 0)R)TAB1
GROUP BY user_id;

What does this query do?

• The same table is used twice. The calculation result is available in the outer SELECT.
• The inner SELECT (subquery) does the following:
  • calc time difference (t2-t1)
  • when user_id is the same (user_id = user_id)
  • but different row (id-1)
  • the result is time_difference
• The outer SELECT gets time_difference and SUMs it up

For now, you get time_spent in seconds. If you want your exact result '20:00', you might change the first line to include a time conversion, like so:

SELECT user_id, TIME_FORMAT(SEC_TO_TIME(SUM(time_difference)),'%i') as time_spent
better
SELECT user_id, TIME_FORMAT(SEC_TO_TIME(SUM(time_difference)),'%Hh %im') as time_spent

To get the time_spent for a user_id, you might add

JOIN (SELECT @DIS := 0)R)TAB1 WHERE user_id = 1

SQL FIDDLE with time conversion and WHERE user_id http://sqlfiddle.com/#!2/ab988/9

Answer 2

Here's a php side approach that should get you started. Assuming $arrQueryResults is a DB result set from "SELECT * FROM table ORDER BY user_id, time, action" using whatever syntax is appropriate for your DB.

$db = mysqli_connect("server", "username", "password", "database");
$query = "SELECT * FROM action WHERE date = '".$todaysDate."' ORDER BY user_id, time, action";
$result = mysqli_query($db, $query);

while ($arrResult = mysqli_fetch_assoc($result)) { // For each row in the query result
    $user=$arrResult['user_id'];
    $time=strtotime($arrResult['time']); // Converts H:M:S to an integer timestamp
    $action=$arrResult['action'];

    // Add the timestamp for this user and action to track until we have a matched pair
    if ($action == '2') {
        $arrUserAction[$user][$action]=$time;
    }

    if ($action == '5' && isset($arrUserAction[$user]['2'])) {
        if (!isset($arrDiffSums[$user])) {  // If we have no running total for the user yet, create one.
            $arrDiffSums[$user]=$time - $arrUserAction[$user]['2'];
        } else { // IF we have a total for the user already, add to it.
            $arrDiffSums[$user]=$arrDiffSums[$user] + ($time - $arrUserAction[$user]['2']);
        }
        unset($arrUserAction[$user]);  // We just added a sum, now remove our tracking to be ready for the next pair.
    } 
}
print_r($arrDiffSums);

This should result in a $arrDiffSums with # secs by user. In your 2nd table example:

array(
  '1' => 1200,
  '2' => 1200,
  '3' => 600
)

Which can be converted to H:M:S or other formats with gmdate(); (PHP - Convert seconds to Hour:Minute:Second):

foreach ($arrDiffSums as $user => $secs) {
     print "User: ".$user." Sum: ".gmdate("H:i:s", $secs)."\n";
}