DB2 window function - running total of X for preceding N minutes worth of rows

Question

Very new to window functions but think I get the idea.

I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column)

A working sample might would look like this:

EDIT: fiddle link

Filtered for T2 highlights it better:

There's plenty about the topic like this or this and so on but I'm having trouble finding a solution specific to my requirements.

If I didn't need it by 10 minute intervals it'd be very simple, by 10 rows (current + 9 preceding);

SELECT  ID, TIME, P/L,
    SUM(P/L) OVER (PARTITION BY ID
                     ORDER BY TIME
                     ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as ROLLING
FROM TABLE

Maybe adding the 10 min constraint is not possible with the window function?

############## EDIT TWO ##############

Ok, so I've figure out the issue and managed to reproduce it in a fiddle

In the real table there are valid duplicate transactions (same ID and TIME), I thought I could pull back microseconds and solve this but unfortunately these aren't stored.

We end up with a weird backwards sum for the dupe's which may not be a huge issue but would rather fix if possible:

########################################

Answer 1

Db2 does not support the INTERVAL keyword on OLAP window-aggregation-group-clause https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html so you need to convert the TIMESTAMPs to MINIUTEs first. E.g

    ID
,   "TIME"
,   "P/L"
,   SUM("P/L") OVER (PARTITION BY ID
                 ORDER BY (DAYS("TIME") * 84000::BIGINT + MIDNIGHT_SECONDS("TIME"))/60
                 RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) as ROLLING
FROM 
    TABLE(VALUES 
        ('T1','2020-01-07-15.10.00',1.2)
    ,   ('T1','2020-01-07-15.25.00',2.2)
    ,   ('T1','2020-01-07-15.30.00',3.2)
    ,   ('T1','2020-01-07-15.40.00',4.2)
    ) AS T(ID, "TIME", "P/L")

REturns

ID|TIME               |P/L|ROLLING
--|-------------------|---|-------
T1|2020-01-07-15.10.00|1.2|    1.2
T1|2020-01-07-15.25.00|2.2|    2.2
T1|2020-01-07-15.30.00|3.2|    5.4
T1|2020-01-07-15.40.00|4.2|    7.4

Answer 2

You would use RANGE, not ROWS, after converting the timestamp to a numeric value:

SELECT  ID, tm, P/L,
    SUM(P/L) OVER (PARTITION BY ID
                     ORDER BY extract(epoch from tm)
                     range BETWEEN 600 PRECEDING AND CURRENT ROW) as ROLLING
FROM tbl

This will work with Db2 for LUW 11.1 and later.