Incrementing row numbers by condition in postgres

Question

I have a postgres table with timestamps and the rounded difference in hours between current and previous (lagged) timestamp in difftime

 timestamp               type    difftime
 2013-09-14 14:19:46     JPR03   2 
 2013-09-14 15:11:48     JPR03   1 
 2013-09-14 16:11:49     JPR03   1 
 2013-09-14 17:13:45     JPR03   1 
 2013-09-22 00:08:38     JPR03   175 
 2013-09-22 00:10:11     JPR03   0 
 2013-09-22 01:11:36     JPR03   1 
 2013-09-22 02:16:11     JPR03   1 
 2013-09-22 03:13:16     JPR03   1 
 2013-09-22 04:05:38     JPR03   1 
 2013-09-22 06:10:11     JPR03   2 
 2013-09-22 07:26:43     JPR03   1 
 2013-09-22 08:17:35     JPR03   1 
 2013-09-22 09:16:08     JPR03   1 
 2013-09-22 10:16:08     JPR03   1 
 2013-10-01 06:15:07     JPR03   212 
 2013-10-01 06:15:12     JPR03   0 
 2013-10-02 07:15:15     JPR03   25 
 2013-10-02 08:05:09     JPR03   1 

My objective is to create an incremental row number sequence that increases by 1 when and only when the value in difftime is above a certain threshold x (ordered by time). If x = 5, then the output would look like this:

 timestamp               type    difftime  rownum
 2013-09-14 14:19:46     JPR03   2         0
 2013-09-14 15:11:48     JPR03   1         0
 2013-09-14 16:11:49     JPR03   1         0
 2013-09-14 17:13:45     JPR03   1         0
 2013-09-22 00:08:38     JPR03   175       1
 2013-09-22 00:10:11     JPR03   0         1
 2013-09-22 01:11:36     JPR03   1         1
 2013-09-22 02:16:11     JPR03   1         1
 2013-09-22 03:13:16     JPR03   1         1
 2013-09-22 04:05:38     JPR03   1         1
 2013-09-22 06:10:11     JPR03   2         1
 2013-09-22 07:26:43     JPR03   1         1
 2013-09-22 08:17:35     JPR03   1         1
 2013-09-22 09:16:08     JPR03   1         1
 2013-09-22 10:16:08     JPR03   1         1
 2013-10-01 06:15:07     JPR03   212       2
 2013-10-01 06:15:12     JPR03   0         2
 2013-10-02 07:15:15     JPR03   25        3
 2013-10-02 08:05:09     JPR03   1         3

I am familiar with the RANK(), DENSE_RANK(), ROW_NUMBER(), and COALESCE() functions, but none of these would achieve the objective of incrementing a row number by condition (beginning with 0). Any suggestions on how to implement this kind of variable assignment or what functions might be applied here to partition based on a condition?

Answer 1

demo:db<>fiddle

You can use the cumulative SUM() function with a conditional value: Add 1 if the condition is met, 0 otherwise:

SELECT
    *,
    SUM(
        CASE 
            WHEN diff >= 5 THEN 1
            ELSE 0
        END
    ) OVER (ORDER BY ts)
FROM --<your query>