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?

To clarify question, if you have row with time = '00:03:40' in the above example, will it have group number 2 and other groups shift by +1? Trying to understand what do you mean by +/-3 minutes difference — Roman Pekar, Sep 13 '13 at 10:44

Clodoaldo Neto · Accepted Answer · 2013-09-13T10:43:08.097

5

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.

edited Sep 13 '13 at 10:43

answered Sep 13 '13 at 10:30

Clodoaldo Neto

118,695
26
233
260

Nice and simple. I'll suggest a slight improvement. – Erwin Brandstetter Sep 13 '13 at 11:38

score 1 · Answer 2 · answered Sep 13 '13 at 10:34

1

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

answered Sep 13 '13 at 10:34

Tomas Greif

21,685
23
106
155

Erwin Brandstetter · Answer 3 · 2022-05-25T00:11:17.320

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.

misha · Answer 4 · 2013-09-17T07:40:29.723

http://www.depesz.com/2010/09/12/how-to-group-messages-into-chats/

should use windowing. it is a example from textbook

with
  xinterval( val ) as ( select 2 ),
  data( id, t ) as 
  (
    values  

      ( 1000, 1 ),
      ( 1001, 2 ),
      ( 1002, 3 ),

      ( 1000, 7 ),
      ( 1003, 8 )

  ),  
  x( id, t, tx ) as
  (
    select id, t,
      case (t - lag(t) over (order by t)) > xinterval.val
        when true then t when null then t
      end
    from data natural join xinterval
  ),
  xx( id, t, t2 ) as
  (
    select id, t, max(tx) over (order by t) from x
  )
select id, t, text( min(t) over w ) || '-' || text( max(t) over w ) as xperiod
from xx
window w as ( partition by t2 )
order by t

Could add some part of the links in case the link is gone some day. — surfmuggle, Sep 13 '13 at 18:32

score 0 · Answer 5 · answered Jun 14 '14 at 05:31

Make function

CREATE OR REPLACE FUNCTION public.date_round (
  base_date timestamp,
  round_interval interval
)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS
$body$
DECLARE
   res TIMESTAMP;
BEGIN   
    res := TIMESTAMP 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2)
                / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second';            
    IF (base_date > res ) THEN
        res := res + $2;
    END IF;
    RETURN res;
END;
$body$
LANGUAGE 'plpgsql'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

And group by this function result

SELECT t.* FROM (SELECT p.oper_date, date_round(p.oper_date, '5 minutes') as grp FROM test p) t GROUP BY t.grp

This easy :)

Group by floating date range

5 Answers5