MySQL calculate time difference between multiple rows based on status field

Question

I'm trying to calculate total logged in time for individual users. I have the following MySQL table:

user_id | timelog             | status
------- | ------------------- | ------
472     | 2017-07-18 08:00:00 | login
472     | 2017-07-18 09:00:00 | break start
472     | 2017-07-18 09:30:00 | break end
472     | 2017-07-18 10:00:00 | logout
472     | 2017-07-18 11:00:00 | login
472     | 2017-07-18 14:00:00 | logout

The client wants to calculate the time the user has spent logged in within selected date. While doing some case studies, I was able to calculate the time between the first login/logout:

SELECT
  TIMEDIFF(
    (SELECT timelog FROM qc_user_status_logs WHERE status = 'logout' AND user_id = '472' AND timelog LIKE '2017-07-18%' LIMIT 0,1),
    (SELECT timelog FROM qc_user_status_logs WHERE status = 'login' AND user_id = '472' AND timelog LIKE '2017-07-18%' LIMIT 0,1)
) as loggedInTime

However, as you can see from the example data, the user can have multiple logings/logouts during the day, as well as multiple break times. How would I aggregate the logged in time using MySQL only. I've accomplished this using PHP, however because of server performance issues (there's a lot of records), I have to figure out the way to get the total time calculated in MySQL.

Answer 1

This isn't a straightforward query, so, let's do it with a step-by-step approach:

Scenario

CREATE TABLE qc_user_status_logs
(
    user_id integer, 
    timelog datetime, 
    status  varchar(15)
) ;
INSERT INTO qc_user_status_logs
    (user_id, timelog, status)
VALUES
    -- Your example data
    (472, '2017-07-18 08:00:00', 'login'),
    (472, '2017-07-18 09:00:00', 'break start'),
    (472, '2017-07-18 09:30:00', 'break end'),
    (472, '2017-07-18 10:00:00', 'logout'),
    (472, '2017-07-18 11:00:00', 'login'),
    (472, '2017-07-18 14:00:00', 'logout'),
    -- An extra user
    (532, '2017-07-18 09:00:00', 'login'),
    (532, '2017-07-18 09:30:00', 'logout'),
    -- And another entry for a user that doesn't log out 
    -- (i.e.: it is *now* logged in)
    (654, now() - interval 33 minute, 'login');

---

Step 1

For each login, find the corresponding logout via subquery (in MariaDB, you'd use a window function)

SELECT
    user_id, 
    timelog AS login_time, 
    coalesce(
      (SELECT timelog 
         FROM qc_user_status_logs t_out 
        WHERE     t_out.user_id = t_in.user_id 
              AND t_out.timelog >= t_in.timelog 
              AND t_out.status = 'logout'
      ORDER BY timelog 
        LIMIT 1
      ), 
      now()
    ) AS logout_time
FROM
    qc_user_status_logs t_in
WHERE
    status = 'login'
ORDER BY
    user_id, timelog ;

user_id | login_time          | logout_time        
------: | :------------------ | :------------------
    472 | 2017-07-18 08:00:00 | 2017-07-18 10:00:00
    472 | 2017-07-18 11:00:00 | 2017-07-18 14:00:00
    532 | 2017-07-18 09:00:00 | 2017-07-18 09:30:00
    654 | 2017-07-21 23:38:53 | 2017-07-22 00:11:53

---

Step 2

Convert 'login/logout' times to 'logged in' intervals. Best way is to convert times to unix_times and subtract. The result will be number of seconds between login and logout:

SELECT
    user_id, 
    login_time, 
    logout_time, 
    timediff(logout_time, login_time) AS logged_in_time,
    unix_timestamp(logout_time) - unix_timestamp(login_time) AS seconds_logged_in_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'logout'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'login'
    ) AS q1
ORDER BY
    user_id, login_time ;

user_id | login_time          | logout_time         | logged_in_time | seconds_logged_in_time
------: | :------------------ | :------------------ | :------------- | ---------------------:
    472 | 2017-07-18 08:00:00 | 2017-07-18 10:00:00 | 02:00:00       |                   7200
    472 | 2017-07-18 11:00:00 | 2017-07-18 14:00:00 | 03:00:00       |                  10800
    532 | 2017-07-18 09:00:00 | 2017-07-18 09:30:00 | 00:30:00       |                   1800
    654 | 2017-07-21 23:38:53 | 2017-07-22 00:11:53 | 00:33:00       |                   1980

---

Step 3

From the previous query, aggregate (add) logged in intervals, grouping by user

SELECT
    user_id, 
    sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_logged_in_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'logout'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'login'
    ) AS q1
GROUP BY
    user_id
ORDER BY
    user_id ;

user_id | total_seconds_logged_in_time
------: | ---------------------------:
    472 |                        18000
    532 |                         1800
    654 |                         1980

---

Step 4

We perform the same thing for breaks

SELECT
    user_id, 
    sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_break_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'break end'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'break start'
    ) AS q1
GROUP BY
    user_id
ORDER BY
    user_id ;

user_id | total_seconds_break_time
------: | -----------------------:
    472 |                     1800

---

Final step:

Take the (step 3 query) and LEFT JOIN it with the (step 4 query) ON user_id, so that we have all the information corresponding to every user_id together.

Subtract the total_seconds_break_time (or 0, if there isn't any break; by using coalesce).

That will give you the final result:

SELECT
    q10.user_id, 
    q10.total_seconds_logged_in_time - 
        coalesce(q20.total_seconds_break_time, 0) AS net_total_seconds_logged_in_time
FROM    
    (SELECT
        user_id, 
        sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_logged_in_time
    FROM
        (SELECT
            user_id, 
            timelog AS login_time, 
            coalesce(
              (SELECT timelog 
                 FROM qc_user_status_logs t_out 
                WHERE     t_out.user_id = t_in.user_id 
                      AND t_out.timelog >= t_in.timelog 
                      AND t_out.status = 'logout'
              ORDER BY timelog 
                LIMIT 1
              ), 
              now()
            ) AS logout_time
        FROM
            qc_user_status_logs t_in
        WHERE
            status = 'login'
        ) AS q1
    GROUP BY
        user_id
    ) AS q10
    LEFT JOIN
    (SELECT
        user_id, 
        sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_break_time
    FROM
        (SELECT
            user_id, 
            timelog AS login_time, 
            coalesce(
              (SELECT timelog 
                 FROM qc_user_status_logs t_out 
                WHERE     t_out.user_id = t_in.user_id 
                      AND t_out.timelog >= t_in.timelog 
                      AND t_out.status = 'break end'
              ORDER BY timelog 
                LIMIT 1
              ), 
              now()
            ) AS logout_time
        FROM
            qc_user_status_logs t_in
        WHERE
            status = 'break start'
        ) AS q1
    GROUP BY
        user_id
    ) AS q20 ON q20.user_id = q10.user_id
ORDER BY
    q10.user_id ;

user_id | net_total_seconds_logged_in_time
------: | -------------------------------:
    472 |                            16200
    532 |                             1800
    654 |                             1980

Can find everything at dbfiddle here