clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805483 fiddles created (40771 in the last week).

CREATE TABLE tmp ( date timestamp, id_type integer );
 hidden batch(es)


INSERT INTO tmp ( date, id_type ) 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 );
16 rows affected
 hidden batch(es)


WITH IdTypes AS ( SELECT date, id_type, Row_Number() OVER (ORDER BY date) - Row_Number() OVER (PARTITION BY id_type ORDER BY date) AS Seq FROM tmp ) SELECT Min(date) AS begin, Max(date) AS end, id_type FROM IdTypes GROUP BY id_type, Seq ORDER BY begin ;
begin end id_type
2017-01-10 07:19:21 2017-01-10 07:19:25 3
2017-01-10 07:19:26 2017-01-10 07:19:26 5
2017-01-10 07:19:27.1 2017-01-10 07:19:27.1 3
2017-01-10 07:19:28 2017-01-10 07:19:29 5
2017-01-10 07:19:30.1 2017-01-10 07:19:30.1 3
2017-01-10 07:19:31 2017-01-10 07:19:31 5
2017-01-10 07:19:32 2017-01-10 07:19:32 3
2017-01-10 07:19:33.1 2017-01-10 07:19:37.1 5
 hidden batch(es)