MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sessions
    (`uid` int, `time_start` datetime, `time_end` datetime)
INSERT INTO sessions
    (`uid`, `time_start`, `time_end`)
VALUES
    (1, '2018-01-02 04:00:00', '2018-01-02 04:30:00'),
    (1, '2018-01-02 04:25:00', '2018-01-02 04:35:00'),
    (1, '2018-01-02 04:55:00', '2018-01-02 05:15:00'),
    (1, '2018-01-03 04:00:00', '2018-01-03 04:40:00'),
    (1, '2018-01-03 04:25:00', '2018-01-03 04:35:00'),
    (1, '2018-01-03 04:55:00', '2018-01-03 05:15:00'),
    (1, '2018-01-04 04:00:00', '2018-01-04 04:45:00'),
    (1, '2018-01-04 04:25:00', '2018-01-04 04:40:00'),
    (1, '2018-01-04 04:30:00', '2018-01-04 05:15:00')
WITH sessions AS 
    (SELECT uid,
            GREATEST(time_start, COALESCE(MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '2000-01-01')) AS start,
            MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS UNBOUNDED PRECEDING)  AS end
            FROM sessions)
SELECT *, TO_SECONDS(end) - TO_SECONDS(start) AS len 
FROM sessions
-- WHERE DATE(start) = '2018-01-03'


uid
start
end
len


1
2018-01-02 04:00:00.000000
2018-01-02 04:30:00
1800

1
2018-01-02 04:30:00.000000
2018-01-02 04:35:00
300

1
2018-01-02 04:55:00.000000
2018-01-02 05:15:00
1200

1
2018-01-03 04:00:00.000000
2018-01-03 04:40:00
2400

1
2018-01-03 04:40:00.000000
2018-01-03 04:40:00
0

1
2018-01-03 04:55:00.000000
2018-01-03 05:15:00
1200

1
2018-01-04 04:00:00.000000
2018-01-04 04:45:00
2700

1
2018-01-04 04:45:00.000000
2018-01-04 04:45:00
0

1
2018-01-04 04:45:00.000000
2018-01-04 05:15:00
1800


WITH sessions AS 
    (SELECT uid,
            GREATEST(time_start, COALESCE(MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '2000-01-01')) AS start,
            MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS UNBOUNDED PRECEDING)  AS end
            FROM sessions)
SELECT uid, DATE(start) AS `date`, SEC_TO_TIME(SUM(TO_SECONDS(end) - TO_SECONDS(start))) AS totaltime
FROM sessions
GROUP BY uid, `date`


uid
date
totaltime


1
2018-01-02
00:55:00

1
2018-01-03
01:00:00

1
2018-01-04
01:15:00

uid	start	end	len
1	2018-01-02 04:00:00.000000	2018-01-02 04:30:00	1800
1	2018-01-02 04:30:00.000000	2018-01-02 04:35:00	300
1	2018-01-02 04:55:00.000000	2018-01-02 05:15:00	1200
1	2018-01-03 04:00:00.000000	2018-01-03 04:40:00	2400
1	2018-01-03 04:40:00.000000	2018-01-03 04:40:00	0
1	2018-01-03 04:55:00.000000	2018-01-03 05:15:00	1200
1	2018-01-04 04:00:00.000000	2018-01-04 04:45:00	2700
1	2018-01-04 04:45:00.000000	2018-01-04 04:45:00	0
1	2018-01-04 04:45:00.000000	2018-01-04 05:15:00	1800