By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Table1234
(id int, tstamp timestamp, device_type varchar(6))
;
INSERT INTO Table1234
(id, tstamp, device_type)
VALUES
(1, '2017-10-01 00:00:00', 'mobile'),
(2, '2017-10-01 00:00:10', 'mobile'),
(3, '2017-10-01 00:00:20', 'mobile'),
(4, '2017-10-01 00:10:10', 'mobile'),
(5, '2017-10-01 00:10:20', 'mobile')
;
5 rows affected
SELECT tstamp,
SUM( CASE WHEN lag_in_sec >= interval '60' second THEN 1 ELSE 0 END )
OVER (order by tstamp) as group_number
FROM (
SELECT *, tstamp - lag( tstamp ) Over (order by tstamp) as lag_in_sec
FROM Table1234
) x;
| tstamp | group_number |
|---|---|
| 2017-10-01 00:00:00 | 0 |
| 2017-10-01 00:00:10 | 0 |
| 2017-10-01 00:00:20 | 0 |
| 2017-10-01 00:10:10 | 1 |
| 2017-10-01 00:10:20 | 1 |
SELECT min( tstamp ) as min_tstamp,
max( tstamp ) as max_tstamp
FROM (
SELECT tstamp,
SUM( CASE WHEN lag_in_sec >= interval '60' second THEN 1 ELSE 0 END )
OVER (order by tstamp) as group_number
FROM (
SELECT *, tstamp - lag( tstamp ) Over (order by tstamp) as lag_in_sec
FROM Table1234
) x
) y
GROUP BY group_number
ORDER BY 1
| min_tstamp | max_tstamp |
|---|---|
| 2017-10-01 00:00:00 | 2017-10-01 00:00:20 |
| 2017-10-01 00:10:10 | 2017-10-01 00:10:20 |