Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
You can use the peek() function in your script.
Please provide more information how data looks like?
Here is an example of how the data looks:
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
]
I would expect to see a 10 minute difference between the Out time of LOG 1234 and the In time of LOG 4567
Thanks
I thought this would work but I was unable to get the results I expected; I assume it is because I do not fully understand the peek function.
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
]
Would the logic for the above load statement be:
LOAD LOG
,interval(peek(Out)-In,'mm') as Gap
RESIDENT TIMES;
Thanks again
is it sorted by ID, then by Date?
are there overlapping Date Sections?
Currently sorted by date/time
There is a possibility of overlapping date sections
so you like to see the difference, if the "LOG" changes?
No. I already have the times for each log. Want to see the gap in minutes between each log.
I suppose, that you have dual()-timestamps:
try:
LOAD
LOG,
Interval(Rangesum(-1*Peek('In'),Out),'mm' ) as DELTA
resident TIMES;