clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798208 fiddles created (41239 in the last week).

CREATE TABLE foo ( foo_at timestamp, foo_type integer );
 hidden batch(es)


INSERT INTO foo ( foo_at, foo_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)


create function grp_sfunc(integer[],integer) returns integer[] language sql as $$ select array[$1[1]+($1[2] is distinct from $2 or $1[3]=0)::integer,$2,1]; $$;
 hidden batch(es)


create function grp_finalfunc(integer[]) returns integer language sql as $$ select $1[1]; $$;
 hidden batch(es)


create aggregate grp(integer)( sfunc = grp_sfunc , stype = integer[] , finalfunc = grp_finalfunc , initcond = '{0,0,0}' );
 hidden batch(es)


select min(foo_at) begin_at, max(foo_at) end_at, foo_type from (select *, grp(foo_type) over (order by foo_at) from foo) z group by grp, foo_type order by 1;
begin_at end_at foo_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)