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. 2555381 fiddles created (37426 in the last week).

CREATE TABLE tbl (Hero text, timestamp timestamp); INSERT INTO tbl VALUES ('Batman' , '2016-12-08 12:00:00') , ('Batman' , '2016-12-08 12:07:00') , ('Batman' , '2016-12-08 13:00:00') , ('Batman' , '2016-12-08 14:00:00') , ('Wonder Woman', '2016-12-08 10:15:00') , ('Wonder Woman', '2016-12-08 10:18:00') , ('Wonder Woman', '2016-12-08 10:25:00') , ('Wonder Woman', '2016-12-08 11:30:00');
8 rows affected
 hidden batch(es)


-- 10 minute raster SELECT hero , count(*) AS ct , min(timestamp)::text AS start_time , CASE WHEN count(*) > 1 THEN max(timestamp)::text END AS end_time FROM tbl GROUP BY hero , date_trunc('hour', timestamp) , EXTRACT(MINUTE FROM timestamp)::int / 10 ORDER BY 1, 3;
hero ct start_time end_time
Batman 2 2016-12-08 12:00:00 2016-12-08 12:07:00
Batman 1 2016-12-08 13:00:00
Batman 1 2016-12-08 14:00:00
Wonder Woman 2 2016-12-08 10:15:00 2016-12-08 10:18:00
Wonder Woman 1 2016-12-08 10:25:00
Wonder Woman 1 2016-12-08 11:30:00
 hidden batch(es)


-- gaps of 10 minutes define groups SELECT hero , count(*) AS ct -- optional , min(timestamp)::text AS start_time , CASE WHEN count(*) > 1 THEN max(timestamp)::text END AS end_time FROM ( SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp FROM ( SELECT * , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp) < timestamp - interval '10 min' AS step FROM tbl ) sub1 ) sub2 GROUP BY hero, grp ORDER BY hero, grp;
hero ct start_time end_time
Batman 2 2016-12-08 12:00:00 2016-12-08 12:07:00
Batman 1 2016-12-08 13:00:00
Batman 1 2016-12-08 14:00:00
Wonder Woman 3 2016-12-08 10:15:00 2016-12-08 10:25:00
Wonder Woman 1 2016-12-08 11:30:00
 hidden batch(es)