How can I make an SQL query that returns time differences between checkins and checkouts?

Question

I'm using mysql and I've got a table similar to this one:

id  | user  | task | time                  | checkout
----+-------+------+-----------------------+---------
1   | 1     | 1    | 2014-11-25 17:00:00   | 0
2   | 2     | 2    | 2014-11-25 17:00:00   | 0
3   | 1     | 1    | 2014-11-25 18:00:00   | 1
4   | 1     | 2    | 2014-11-25 19:00:00   | 0
5   | 2     | 2    | 2014-11-25 20:00:00   | 1
6   | 1     | 2    | 2014-11-25 21:00:00   | 1
7   | 1     | 1    | 2014-11-25 21:00:00   | 0
8   | 1     | 1    | 2014-11-25 22:00:00   | 1

id is just an autogenerated primary key, and checkout is 0 if that row registered a user checking in and 1 if the user was checking out from the task.

I would like to know how to make a query that returns how much time has a user spent at each task, that is to say, I want to know the sum of the time differences between the checkout=0 time and the nearest checkout=1 time for each user and task.

Edit: to make things clearer, the results I'd expect from my query would be:

user  | task | SUM(timedifference)
------+------+-----------------
1     | 1    | 02:00:00
1     | 2    | 02:00:00
2     | 2    | 03:00:00

I have tried using SUM(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP(time)), while grouping by user and task to figure out how much time had elapsed, but I don't know how to make the query only sum the differences between the particular times I want instead of all of them.

Can anybody help? Is this at all possible?

Answer 1

As all comments tell you, your current table structure is not ideal. However it's still prossible to pair checkins with checkouts. This is a SQL server implementation but i am sure you can translate it to MySql:

SELECT id
    , user_id
    , task
    , minutes_per_each_task_instance = DATEDIFF(minute, time, (
            SELECT TOP 1 time
            FROM test AS checkout
            WHERE checkin.user_id = checkout.user_id 
                AND checkin.task = checkout.task 
                AND checkin.id < checkout.id 
                AND checkout.checkout = 1
            ))
FROM test AS checkin
WHERE checkin.checkout = 0

Above code works but will become slower and slower as your table starts to grow. After a couple of hundred thousands it will become noticable

I suggest renaming time column to checkin and instead of having checkout boolean field make it datetime, and update record when user checkouts. That way you will have half the number of records and no complex logic for reading or querying

Answer 2

You can determine with a ranking method what are the matching check in/ check out records, and calculate time differences between them

In my example new_table is the name of your table

    SELECT n.user, n.task,n.time, n.checkout , 
           CASE WHEN @prev_user = n.user 
                 AND @prev_task = n.task 
                 AND @prev_checkout = 0 
                 AND n.checkout = 1 
                 AND @prev_time IS NOT NULL
                THEN HOUR(TIMEDIFF(n.time, @prev_time)) END AS timediff,
           @prev_time := n.time,
           @prev_user := n.user, 
           @prev_task := n.task, 
           @prev_checkout := n.checkout
      FROM new_table n, 
           (SELECT @prev_user = 0, @prev_task = 0, @prev_checkout = 0, @prev_time = NULL) a
    ORDER BY user, task, `time`

Then sum the time differences (timediff) by wrapping it in another select

    SELECT x.user, x.task, sum(x.timediff) as total
      FROM ( 
            SELECT n.user, n.task,n.time, n.checkout , 
            CASE WHEN @prev_user = n.user 
                  AND @prev_task = n.task 
                  AND @prev_checkout = 0 
                  AND n.checkout = 1 
                  AND @prev_time IS NOT NULL
                 THEN HOUR(TIMEDIFF(n.time, @prev_time)) END AS timediff,
                  @prev_time := n.time,
                  @prev_user := n.user, 
                  @prev_task := n.task, 
                  @prev_checkout := n.checkout
             FROM new_table n,
                  (@prev_user = 0, @prev_task = 0, @prev_checkout = 0, @prev_time = NULL) a
            ORDER BY user, task, `time`
               ) x
      GROUP BY x.user, x.task

It would probably be easier to understand by changing the table structure though. If that is at all possible. Then the SQL wouldn't have to be so complicated and would be more efficient. But to answer your question it is possible. :)

In the above examples, names prefixed with '@' are MySQL variables, you can use the ':=' to set a variable to a value. Cool stuff ay?

Answer 3

Select MAX of checkouts and checkins independently, map them based on user and task and calculate the time difference

select user, task, 
SUM(UNIX_TIMESTAMP(checkin.time) - UNIX_TIMESTAMP(checkout.time)) from (
(select user, task, MAX(time) as time
from checkouts
where checkout = 0
group by user, task) checkout
inner join
(select user, task, MAX(time) as time
from checkouts
where checkout = 1
group by user, task) checkin 
on (checkin.time > checkout.time 
and checkin.user = checkout.user 
and checkin.task = checkout.task)) c

Answer 4

This should work. Join on the tables and select the minimum times

SELECT 
  `user`,
  `task`,
  SUM(
    UNIX_TIMESTAMP(checkout) - UNIX_TIMESTAMP(checkin)
  ) 
FROM
  (SELECT 
    so1.`user`,
    so1.`task`,
    MIN(so1.`time`) AS checkin,
    MIN(so2.`time`) AS checkout 
  FROM
    so so1 
    INNER JOIN so so2 
      ON (
        so1.`id` = so2.`id` 
        AND so1.`user` = so2.`user` 
        AND so1.`task` = so2.`task` 
        AND so1.`checkout` = 0 
        AND so2.`checkout` = 1 
        AND so1.`time` < so2.`time`
      ) 
  GROUP BY `user`,
    `task`,
    so1.`time`) a 
GROUP BY `user`,
  `task` ;

As others have suggested though, This will not scale too well as it is, you would need to adjust it if it starts handling more data