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.