Group by floating date range

Question

I'm using PostgreSQL 9.2.
I have table containing time of some devices getting out of service.

+----------+----------+---------------------+
| event_id |  device  |         time        |
+----------+----------+---------------------+
|        1 | Switch4  | 2013-09-01 00:01:00 |
|        2 | Switch1  | 2013-09-01 00:02:30 |
|        3 | Switch10 | 2013-09-01 00:02:40 |
|        4 | Switch51 | 2013-09-01 03:05:00 |
|        5 | Switch49 | 2013-09-02 13:00:00 |
|        6 | Switch28 | 2013-09-02 13:01:00 |
|        7 | Switch9  | 2013-09-02 13:02:00 |
+----------+----------+---------------------+

I want the rows to be grouped by +/-3 minutes' time difference, like that:

+----------+----------+---------------------+--------+
| event_id |  device  |         time        |  group |
+----------+----------+---------------------+--------+
|        1 | Switch4  | 2013-09-01 00:01:00 |      1 |
|        2 | Switch1  | 2013-09-01 00:02:30 |      1 |
|        3 | Switch10 | 2013-09-01 00:02:40 |      1 |
|        4 | Switch51 | 2013-09-01 03:05:00 |      2 |
|        5 | Switch49 | 2013-09-02 13:00:00 |      3 |
|        6 | Switch28 | 2013-09-02 13:01:00 |      3 |
|        7 | Switch9  | 2013-09-02 13:02:00 |      3 |
+----------+----------+---------------------+--------+

I tried to make it using window function, but in clause

[ RANGE | ROWS ] BETWEEN frame_start AND frame_end, where frame_start and frame_end can be one of UNBOUNDED PRECEDING value PRECEDING CURRENT ROW value FOLLOWING UNBOUNDED FOLLOWING,

value must be an integer expression not containing any variables, aggregate functions, or window functions

So, considering this, I'm not able to indicate the time interval. Now I doubt that window function can resolve my problem. Could you help me?

Answer 1

SQL Fiddle

select
    event_id, device, ts,
    floor(extract(epoch from ts) / 180) as group
from t
order by ts

It is possible to make the group number a sequence starting at 1 using a window function but it is a not small cost that I don't know if is necessary. This is it

select
    event_id, device, ts,
    dense_rank() over(order by "group") as group
from (
    select
        event_id, device, ts,
        floor(extract(epoch from ts) / 180) as group
    from t
) s
order by ts

time is a reserved word. Pick another one as the column name.

Answer 2

SQLFiddle

with u as (
select 
   *,
   extract(epoch from ts - lag(ts) over(order by ts))/ 60 > 180 or lag(ts) over(order by ts) is null as test
from
   t
   )

   select *, sum(test::int) over(order by ts) from u

Answer 3

This is just a slight improvement over @Clodoaldo's basically good answer.

To get sequential group numbers:

SELECT event_id, device, ts
     , dense_rank() OVER (ORDER BY trunc(extract(epoch from ts) / 180)) AS grp
FROM   tbl
ORDER  BY ts;

• Using ts instead of the (partly) reserved word time is good advice. So don't use the reserved word group either. Using grp instead.

• Sequential numbers can be had without subquery.

• Use trunc() instead of floor(). Both are good, trunc() is slightly faster.