clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
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. 599315 fiddles created (18765 in the last week).

CREATE TABLE tmp ( date timestamp UNIQUE NOT NULL, 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)


CREATE OR REPLACE FUNCTION f_tmp_groups() RETURNS TABLE (id_type int, grp_begin timestamp, grp_end timestamp) AS $func$ DECLARE _row tmp; -- use table type for row variable BEGIN FOR _row IN TABLE tmp ORDER BY date -- add more columns to make order deterministic LOOP CASE _row.id_type = id_type WHEN TRUE THEN -- same group continues grp_end := _row.date; -- remember last date so far WHEN FALSE THEN -- next group starts RETURN NEXT; -- return result for last group id_type := _row.id_type; grp_begin := _row.date; grp_end := _row.date; ELSE -- NULL for 1st row id_type := _row.id_type; -- remember row data for starters grp_begin := _row.date; grp_end := _row.date; END CASE; END LOOP; RETURN NEXT; -- return last result row END $func$ LANGUAGE plpgsql;
 hidden batch(es)


SELECT * FROM f_tmp_groups();
id_type grp_begin grp_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)