# How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each.

So a table that looks like this:

``````| Hero         | timestamp           |

| 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 10:30:00 |
``````

would result in a view like this

``````| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00 |
``````

or this:

``````| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 13:00:00 | NULL                |
| Batman       | 2016-12-08 14:00:00 | NULL                |
| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00|
``````

Either solution would be fine.

• The last result for 'Wonder Woman' in each result has more than an hour between rows and contradicts the rest of your question. Please clarify the criteria to group rows. Commented Dec 8, 2016 at 23:26
• Sorry, you are totally right. I fixed the last line. Commented Dec 9, 2016 at 10:06
• So, if I understand well, what you wish is to find the start and end times of a sequence of events, with a gap between events no larger than 10 minutes. Is it right? That is: Wonder Woman gets just one entry because in the sequence: 10:15, 10:18, 10:25 and 10:30 there is never a gap larger than 10 minutes, even if those events are not all in the same "ten minutes bin". Commented Dec 9, 2016 at 12:31
• That is correct. The second solution from @ErwinBrandstetter solves exactly this. Commented Dec 9, 2016 at 17:32

### 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
``````

Refer to the chapter Date/Time Functions and Operators in the manual.

`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:

db<>fiddle here
Old sqlfiddle

• Hm, this does not work in any case as expected right? If the minute part isn't in the same decimal (ie. 20:10:00, 20:19:00 and 20:25:00) the query begins a new group. Commented Dec 8, 2016 at 20:46
• Depends on what you expected. I am grouping along 10-minute intervals like you mentioned in your question. If that's not what you want ask a question with an exact definition of how to form groups. Commented Dec 8, 2016 at 21:28
• Your solution is perfect. Hoassn Dampf! Commented Dec 9, 2016 at 10:08

If instead of rounding time intervals you would like to round integer numbers, let's say to "the nearest dozen", you would do the follwing operation:

``````number::integer / 12 * 12
``````

Although dividing and multiplying by 12 seems to "just do nothing", it actually gets rid of the "fraction of a dozen" in your number. The trick lies in the fact that your division is an integer division, and the result is an integer number with no fractional part.

Try, for instance:

``````SELECT
1 / 12 * 12 AS n1,
2 / 12 * 12 AS n2,
11 / 12 * 12 AS n11,
12 / 12 * 12 AS n12,
13 / 12 * 12 AS n13,
23 / 12 * 12 AS n23,
24 / 12 * 12 AS n24,
25 / 12 * 12 AS n25,
35 / 12 * 12 AS n35 ;
``````

This is a standard way to "rounding down" numbers to bin them, given a certain bin size (12, in this case).

You can do the same with times, as sooun as you convert timestamps to integers (or bigints). You do that by representing timestamps as "number of seconds since a certain date and time". The "epoch" of a timestamp represents the number of seconds elapsed since a certain time and date (1st Jan 1970, 00:00:00). PostgreSQL (and I think, SQL in general) uses "EXTRACT (EPOCH FROM ts)" to get this value.

If you want to round these epochs to any interval (in your case 10 minutes = 60 * 10 seconds), you can make an integer division of this epoch by 600 (so, you get rid of the decimals) and multipy back by this 600. This way, you get rid of the "fractional part" of your 10 minute intervals.

The following code will give you the second version of your possible solutions (with a couple of extra rows for Wonder Woman):

``````CREATE TABLE hero_stamps (hero text, ts timestamp without time zone) ;

INSERT INTO
hero_stamps (hero, ts)
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') ;

SELECT
hero,
start_time,
(CASE WHEN start_time = end_time THEN NULL ELSE end_time END) AS end_time
FROM
(
SELECT
hero,
extract(epoch from ts)::bigint   /* timestamp converted to epoch */
/ (60 * 10)::integer          /* integer div */
* (60 * 10)::integer          /* multiply back to have start time */
AS epoch_start_time_of_interval,
min(ts) AS start_time,
max(ts) AS end_time
FROM
hero_stamps
GROUP BY
hero, epoch_start_time_of_interval
) AS s0
ORDER BY
hero, start_time ;
``````

The GROUP BY makes the "binning", the min(ts) and max(ts) gives you the maximum and minimum values, and the extra function in the outermost SELECT gives the nulls the way you want (and gets rid of the "epoch_start_time_of_interval" that you're not interested in).

This approach will work even if you have timestamps with the same "hour" and "minute" but different calendar days, because it does not ignore this information (it gets embedded in the "epoch").

A more compact version (giving the same results) would be

``````SELECT
hero,
min(ts) AS start_time,
(CASE WHEN count(*) = 1 THEN NULL ELSE max(ts) END) AS end_time
FROM
hero_stamps
GROUP BY
hero,
extract(epoch from ts)::bigint / 600
ORDER BY
1, 2 ;
``````
• This seems like a verbose copy of my first query. The only variation is extracting the epoch - one column less to group by, ok. Aside: `CASE WHEN start_time = end_time` in the long version is subtly different from `CASE WHEN count(*) = 1` in the "compact" version. Commented Dec 8, 2016 at 23:45
• Seems like you are misunderstanding `date_trunc()`. There is no confusion among days. Try it. Commented Dec 9, 2016 at 4:12
• You're right!, I was confusing date_trunc() with date_part(). Thanks for the clarification. Your solution and mine give actually the same results. The (not very practical) difference would be that converting to epoch gives you the ability to make your intervals of any number of seconds (let's say 137), which in practice I don't think is used in practice. Commented Dec 9, 2016 at 11:33