SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE wi (uid INT, start text, stop text); INSERT INTO wi VALUES (1, '17.01.2020 11:34', '17.01.2020 11:43'); INSERT INTO wi VALUES (1, '17.01.2020 11:43', '17.01.2020 11:59'); INSERT INTO wi VALUES (1, '17.01.2020 12:25', '17.01.2020 12:47'); INSERT INTO wi VALUES (1, '17.01.2020 16:48', '17.01.2020 17:11'); INSERT INTO wi VALUES (1, '17.01.2020 17:16', '17.01.2020 17:20'); INSERT INTO wi VALUES (2, '17.01.2020 17:22', '17.01.2020 17:40'); INSERT INTO wi VALUES (2, '17.01.2020 17:26', '17.01.2020 17:40'); INSERT INTO wi VALUES (2, '17.01.2020 18:38', '17.01.2020 18:41'); INSERT INTO wi VALUES (2, '17.01.2020 18:39', '17.01.2020 18:58'); INSERT INTO wi VALUES (2, '17.01.2020 19:01', '17.01.2020 19:12'); select uid, start, stop, if(timestampdiff(minute, stop, lead(start) over(partition by uid)) > MINUTE('00:25:00'), 'True', 'False') as time from (SELECT uid, STR_TO_DATE(start, '%d.%m.%Y %H:%i') as start, STR_TO_DATE(stop, '%d.%m.%Y %H:%i') as stop from wi) as wifi; select uid, start, stop, time, dense_rank() over(partition by time) as dense from(select uid, start, stop, if(timestampdiff(minute, stop, lead(start) over(partition by uid)) > MINUTE('00:25:00'), 'True', 'False') as time from (SELECT uid, STR_TO_DATE(start, '%d.%m.%Y %H:%i') as start, STR_TO_DATE(stop, '%d.%m.%Y %H:%i') as stop from wi) as wifi) as wifi_2; select uid, if(time = 'True' and lag(time) over(partition by uid) = 'False', lag(start) over(partition by uid), if(time = 'True' and lag(time) over(partition by uid) = 'True', start, if(time = 'False' and lag(time) over(partition by uid) = 'True', start, 'Null'))) as start, if(time = 'True' and lag(time) over(partition by uid) = 'False', stop, if(time = 'True' and lag(time) over(partition by uid) = 'True', stop, if(time = 'False', stop, 'Null'))) as stop from(select uid, start, stop, if(timestampdiff(minute, stop, lead(start) over(partition by uid)) > MINUTE('00:25:00'), 'True', 'False') as time from (SELECT uid, STR_TO_DATE(start, '%d.%m.%Y %H:%i') as start, STR_TO_DATE(stop, '%d.%m.%Y %H:%i') as stop from wi) as wifi) as wifi_2;