Return to Revisions

###Raster of 10-minute intervals

I suggest to group by a combination of "hour" and 10-minute interval:

SELECT hero
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM   tbl
GROUP  BY hero
     , date_trunc('hour', timestamp)
     , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 2;  -- optional

Working with date / time functions.

EXTRACT(minute FROM timestamp) extracts the minute part of the time The expression. After the cast to integer (::int), integer division (/ 10) effectively rounds to 10-minute intervals (0 - 5).

The CASE expression only adds an end_time if more than one rows fall in the same 10-minute interval.

I advise not to use the "timestamp" as identifier. It's a reserved word in standard SQL and base data type in Postgres.

###Groups defined by gaps of 10 or more minutes If "groups" are defined by gaps of 10 minutes or more between rows of the same hero:

SELECT hero
     , count(*) AS ct  -- optional
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) 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;

Detailed explanation:

• Select longest continuous sequence

SQL Fiddle for both.