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.