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 from(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' and lead(time) over(partition by uid) = 'True', lead(time) over(partition by uid), if(time = 'False' and lead(time) over(partition by uid) = 'False', last_value(stop) over (partition by uid rows between current row and UNBOUNDED FOLLOWING), 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) as wifi_3 where start is not Null;