Postgres consecutive time intervals

Question

The table:

timestamp
---------------------
2018-01-15 14:31:23
2018-01-15 14:31:25
2018-01-15 14:31:26
2018-01-15 14:31:28
2018-01-15 14:31:29
2018-01-15 14:31:30
2018-01-15 14:31:35

It would be really helpful if someone shows how to get consecutive time intervals using sql. Consequent means that it has seconds followed one by one, if there is a gap in more than 1 second it is not consequent, and should be counted as separate interval.

The following is expected result:

result
--------
1
2
3
1

Not writing your query for you. Show some own attempt! Until you do that and ask a precise question: VTC as too broad. — Marcus Müller
– Marcus Müller, Commented Jan 21, 2018 at 20:19
The question is out of the context, it is a product of some work before, I am not able to share the whole task, the thing is at that point I have no idea how to do it, that is why I am here and asking for help. — Andy
– Andy, Commented Jan 21, 2018 at 20:23

Gordon Linoff · Accepted Answer · 2018-01-21 20:35:52Z

5

I see. You can use row_number() trick for this:

select grp, count(*)
from (select t.*, 
             (time_stamp - row_number() over (order by timestamp) * interval '1 second') as grp
      from t
     ) t
group by grp
order by min(timestamp);

The idea is to subtract a sequence of numbers from the timestamp. Timestamps that are in a sequence will all end up with the same value. That appears to be the groups that you want.

answered Jan 21, 2018 at 20:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gregorp Over a year ago

Just a warning, this query will not work if you have too many entries in the database. It will return an error with timestamp out of range since you will subtract a large integer from the time_stamp and timestamps have a lower bound.

Collectives™ on Stack Overflow

Postgres consecutive time intervals

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related