grouping, PostgreSQL

Language: Layout:

CREATE TABLE dates (ddate timestamp, id_type int);
insert into dates values
('2017-01-10 07:19:21.0',   3),
('2017-01-10 07:19:22.0',   3),
('2017-01-10 07:19:23.1',   3),
('2017-01-10 07:19:24.1',   3),
('2017-01-10 07:19:25.0',   3),
('2017-01-10 07:19:26.0',   5),
('2017-01-10 07:19:27.1',   3),
('2017-01-10 07:19:28.0',   5),
('2017-01-10 07:19:29.0',   5),
('2017-01-10 07:19:30.1',   3),
('2017-01-10 07:19:31.0',   5),
('2017-01-10 07:19:32.0',   3),
('2017-01-10 07:19:33.1',   5),
('2017-01-10 07:19:35.0',   5),
('2017-01-10 07:19:36.1',   5),
('2017-01-10 07:19:37.1',   5);

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
;


absolute service time: 0,45 sec