How can I make a SQL query that returns the time difference between checks and checks?
I am using mysql and I have a table like this:
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 this string has registered the user, and 1 if the user checked the issue.
I would like to know how to make a query that returns the time spent by the user on each task, i.e. I want to know the sum of the time differences between checkout = 0 and closest checkout = 1 time for each user and task.
Edit: To make things clearer, the results I would 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've tried using SUM(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP(time))
rather grouping by user and task to figure out how much time has passed, but I don't know how to make a query, only summing up the differences between the specific points I want, not all of them.
Can anyone please help? Is it possible?
source to share
As all the comments tell you, your current table structure is not perfect. However, it is still available for paired checks with checks. This is a SQL server implementation, but I'm 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
Working on the code, but will get slower and slower as your table grows. After a couple hundred thousand it will become noticeable
I suggest renaming the column time
to checkin
and instead of having a checkout
boolean field, make it datetime and update the record on user checkout. This way you have half the number of records and no complex logic to read or query.
source to share
You can use the ranking method to determine what is the validation / validation of the records and calculate the time difference 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 be easier to understand by changing the structure of the table. If at all possible. Then SQL would not be as complex and more efficient. But you can answer your question. :)
In the examples above, the names prefixed with '@' are MySQL variables, you can use ': =' to set the variable to a value. Cool stuff ay?
source to share
Select MAX checks and checks yourself, match 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
source to share
This should work. Join tables and choose minimum time
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, this won't scale too well as it is needed, you will need to adjust it if it starts to process more data.
source to share