You can use a RECURSIVE function. This set a level for each gap, and then increases it every time id_type changes. with RECURSIVE rdates as ( (select ddate, id_type, 1 lv from dates order by ddate desc limit 1 ) union (select d.ddate, d.id_type, case when r.id_type = d.id_type then r.lv else r.lv + 1 end lv from dates d inner join rdates r on d.ddate < r.ddate order by ddate desc limit 1) ) select min(ddate) StartDate, max(ddate) EndDate, id_type from rdates group by lv, id_type ; +---------------------+---------------------+---------+ | startdate | enddate | id_type | +---------------------+---------------------+---------+ | 10.01.2017 07:19:21 | 10.01.2017 07:19:25 | 3 | | 10.01.2017 07:19:26 | 10.01.2017 07:19:26 | 5 | | 10.01.2017 07:19:27 | 10.01.2017 07:19:27 | 3 | | 10.01.2017 07:19:28 | 10.01.2017 07:19:29 | 5 | | 10.01.2017 07:19:30 | 10.01.2017 07:19:30 | 3 | | 10.01.2017 07:19:31 | 10.01.2017 07:19:31 | 5 | | 10.01.2017 07:19:32 | 10.01.2017 07:19:32 | 3 | | 10.01.2017 07:19:33 | 10.01.2017 07:19:37 | 5 | +---------------------+---------------------+---------+ Check it: http://rextester.com/WCOYFP6623