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. 2805470 fiddles created (40816 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 );
 hidden batch(es)


SELECT id_type, date AS begin, COALESCE(LEAD(prev_date) OVER (ORDER BY date ASC), last_date) AS end FROM ( SELECT id_type, date, LAG(date) OVER (ORDER BY date ASC) AS prev_date, MAX(date) OVER () AS last_date, CASE id_type WHEN LAG(id_type) OVER (ORDER BY date ASC) THEN 0 ELSE 1 END AS is_start FROM tmp ) AS derived WHERE is_start = 1 ORDER BY date ASC ;
id_type begin end
3 2017-01-10 07:19:21 2017-01-10 07:19:25
5 2017-01-10 07:19:26 2017-01-10 07:19:26
3 2017-01-10 07:19:27.1 2017-01-10 07:19:27.1
5 2017-01-10 07:19:28 2017-01-10 07:19:29
3 2017-01-10 07:19:30.1 2017-01-10 07:19:30.1
5 2017-01-10 07:19:31 2017-01-10 07:19:31
3 2017-01-10 07:19:32 2017-01-10 07:19:32
5 2017-01-10 07:19:33.1 2017-01-10 07:19:37.1
 hidden batch(es)