By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE activities (
id INT NOT NULL,
user_id INT NULL,
activity VARCHAR(45),
log_time DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY(id)
)
ENGINE = InnoDB;
INSERT INTO activities
VALUES(6,1,'start', '2021-10-12 10:00:00'),
(2,1,'task' , '2021-10-12 10:10:00'),
(7,1,'task' , '2021-10-12 10:40:00'),
(3,1,'start', '2021-10-12 16:00:00'),
(1,1,'task', '2021-10-12 16:10:00'),
(9,1,'task', '2021-10-14 08:00:00'),
(10,1,'start','2021-10-16 09:00:00'),
(4,1,'task', '2021-10-16 09:10:00'),
(8,2,'start', '2021-10-12 10:00:00'),
(5,2,'task', '2021-10-12 10:20:00');
SELECT user_id, SUM( TIMESTAMPDIFF(MINUTE, COALESCE( lg, log_time ), log_time ) ) AS total_time
FROM (SELECT LAG(log_time) OVER (PARTITION BY user_id, DATE(log_time)
ORDER BY log_time) AS lg,
a.*
FROM activities AS a
ORDER BY log_time) AS aa
WHERE activity != 'start'
GROUP BY user_id
ORDER BY 1
| user_id | total_time |
|---|---|
| 1 | 60 |
| 2 | 20 |