Find time difference in minutes between end time and begin time of consecutive IDs

[andrewmo]

Hello

Can anyone recommend a way to calculate the time difference between the end time of one event and the beginning time of the next event?

For example

LOG 1234

Start 7:20

End 7:40

LOG 5678

Start 7:45

End 8:00

The gap between LOG 1234 end and LOG 5678 start is 5 minutes.

Thanks in advance

[andrewmo]

I tried this but the DELTA returns 00

[Anonymous · Accepted solution]

next try

SET TimestampFormat='M/D/YYYY hh:mm[:ss][.fff]';

TIMES:

LOAD * Inline [
LOG, In, Out
1234, 5/1/2017 07:20, 5/1/2017 07:40
4567, 5/1/2017 07:50, 5/1/2017 08:20
];

LOAD
LOG,
Interval(if(IsNull(Peek('Out')),0,Rangesum(-Peek('Out'),In)),'m') as DELTA,
In,
Out

resident TIMES;

DROP Table TIMES;

[andrewmo]

That appears to do the trick...thanks much!