I want to group time interval based on given tolerance in postgres sql

create table sample(id integer, name varchar(100), timeint time);

insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:02');
insert into sample values(1, 'aaa', '00:00:03');
insert into sample values(1, 'aaa', '00:00:04');
insert into sample values(2, 'bbb', '00:00:01');
insert into sample values(2,'bbb', '00:00:02');
insert into sample values(2, 'bbb', '00:00:03');

select id,name,timeint,count(timeint) from sample
group by id,name,timeint;

——output will be like—-

1   aaa 00:00:01    2
1   aaa 00:00:02    1
1   aaa 00:00:03    1
1   aaa 00:00:04    1
2   bbb 00:00:01    1
2   bbb 00:00:02    1
2   bbb 00:00:03    1

So the above output should be regrouped in such a manner that for same id and name , timeint with 1 sec tolerance should be considered as same

______ expected output _________________

1   aaa 00:00:01    3
1   aaa 00:00:03    2
2   bbb 00:00:01    2
2   bbb 00:00:03    1 

Answer

The general solution to your problem requires a recursive CTE. If all times are accurate to one second and you only want two seconds, there might be a way around that.

However, this solution uses a recursive CTE:

with recursive s as (
      select s.id, s.name, s.timeint, count(*) as cnt,
             row_number() over (partition by id order by timeint) as seqnum
      from sample s
      group by s.id, s.name, s.timeint
     ),
     cte as (
      select id, timeint, name, seqnum, timeint as start_timeint, cnt
      from s
      where seqnum = 1
      union all
      select s.id, s.timeint, s.name, s.seqnum,
             (case when s.timeint <= cte.start_timeint + interval '1 second'
                   then cte.start_timeint else s.timeint
              end),
             s.cnt
      from cte join
           s
           on s.id = cte.id and s.seqnum = cte.seqnum + 1
     )
select  id, name, start_timeint, sum(cnt)
from cte
group by id, name, start_timeint
order by id, start_timeint;

Here is a db<>fiddle.