How to compute for duration between two consecutive timestamps in 1 field?

[mdmaala]

I have a table:

TIME          | GREEN | YELLOW | RED | 
10:16:43              0             0              0
10:16:46              0             0              2
10:16:47              0             0              0
10:35:12              1             0              0
11:20:21              0             0              0

I want to have another column for duration like this:

TIME          | GREEN | YELLOW | RED |  DURATION |
10:16:43              0             0              0         00:00:03     ----------> meaning for 3 minutes, the values were 0 0 0
10:16:46              0             0              2         00:00:01     ----------->meaning for 1 minutes, red is 2
10:16:47              0             0              0         00:18:25     -----------> meaning fro 18:25, values are 0 0 0 
10:35:12              1             0              0         00:25:08     -----------> meaning for 25:08, green is at 1
11:20:21              0             0              0         00:06:43
11:07:04

How will I do this?

[chrisyounger · Accepted solution]

Ignore my other answer, I see you need to carry the time forward not backwards. Try this:

| streamstats window=1 current=f last(_time) as prevTime last(GREEN) as prevGREEN last(YELLOW) as prevYELLOW last(RED) as prevRED
| eval duration = _time - prevTime
| table prevTime prevGREEN prevYELLOW prevRED duration

[mdmaala]

Thank you so much! this worked for me. 🙂

[chrisyounger]

Try this:

| streamstats window=1 current=f last(_time) as prevTime
| eval duration = _time - prevTime

Hope this helps