Getting time differences between sub events in a transaction

[srichansen]

Hi all,

I am have data about bus routes with arrival times at stops and I am trying to find the ride time between the stops.
The data is structured in a way that its pretty easy to get a transaction per trip, but I am battling to now get the ride times between the sub events.

I am using the following search to create the transaction.

index="ndov" ConcessionAreaCode=GVB LinePlanningNumber=2 RecordedArrivalTime!=00:00:00 RecordedDepartureTime!=00:00:00 
| Eval richting=TripNumber % 2 
|  where richting=0 
| Eval rijtijdarr=strptime(RecordedArrivalTime, "%H:%M:%S"), rijtijddep=strptime(RecordedDepartureTime, "%H:%M:%S") 
| transaction ConcessionAreaCode OperationDate LinePlanningNumber TripNumber with mvlist=t

this gives the following transaction events:

"GVB","GVB","2016-12-10","2","2","Nieuw Sloten - Centraal Station","558","1","Centraal Station","05011","BEGIN","Y","2061","21:13:00","21:13:00","21:11:06","21:13:24","20","Y","Y","N","N"
"GVB","GVB","2016-12-10","2","2","Nieuw Sloten - Centraal Station","558","2","Amsterdam, Nieuwezijds Kolk","05070","INTERMEDIATE","N","2061","21:16:10","21:16:28","21:15:08","21:15:31","-67","Y","Y","N","N"
"GVB","GVB","2016-12-10","2","2","Nieuw Sloten - Centraal Station","558","3","Dam","05065","INTERMEDIATE","N","2061","21:17:42","21:18:00","21:15:50","21:16:46","-82","Y","Y","N","N"
"GVB","GVB","2016-12-10","2","2","Nieuw Sloten - Centraal Station","558","4","Spui","05062","INTERMEDIATE","N","2061","21:21:03","21:21:21","21:20:02","21:20:41","-54","Y","Y","N","N"
"GVB","GVB","2016-12-10","2","2","Nieuw Sloten - Centraal Station","558","5","Koningsplein","06076","INTERMEDIATE","N","2061","21:23:08","21:23:26","21:21:33","21:21:55","-105","Y","Y","N","N"

Does anyone know how I can get the time between the sub events based on the arrival and departure timestamp fields in the data.

Thanks in advance!

[srichansen · Accepted solution]

Thanks for the advice guys. I managed to get it working with streamstats. In the end a transaction was not even needed.
I was making it more complex than it needed to be as I was looking at it from the point of view of sql queries.

This was my search in the end.

search....... | streamstats current=f last(rijtijddep) as last_Depart, last(TimingPointName) as last_halte by ConcessionAreaCode OperationDate LinePlanningNumber TripNumber | eval Brijtijd = rijtijdarr-last_Depart, trajectnaam = last_halte+" - "+TimingPointName | where Brijtijd>0 AND Brijtijd<3600 | stats $HTfunct$(Brijtijd) as gemrijtijd by trajectnaam | eval gemrijtijd = gemrijtijd/60

[Richfez]

I think you need to do a streamstats before your transaction. But before you do that, you'll have to sort. A lot. You'll have to get everything in the right order first, then streamstats with various bits attached, then you can transaction. Pseudo-SPL follows...

my search | sort this that theother morethings stuff bother | streamstats window=2 last(stop_time) AS previous_stop_end | eval stop_duration=previous_stop_end-_time | transaction stuffhere.

Hopefully that helps, it's not as fleshed out as a full answer, but if it's enough, it's enough (I can always convert it to an answer). Or maybe it'll be enough that with some work you'll figure it out and post your OWN answer as Answer. 🙂

[srichansen]

I will give it a try and let you know.
thanks!

[niketn]

@srichansen transaction command generates a field called duration which is the difference in seconds betwee first and last event in the transaction. Similar to transaction you can create a search using stats and compute your own duration using min(_time) as FirstStopDepartureTime and max(_time) and LastStopArrivalTime

[srichansen]

@niketnilay Thanks for your response.

Will that not give me the total transaction time per event? I have used mvindex(0) and mvindex(1) to get the total ride time.

I used transaction as that allowed me to group the events effectively per trip. The key fields to get the unique trips are ConcessionAreaCode OperationDate LinePlanningNumber TripNumber. Where I am having difficulty is that the ride time between the stops is arrival(row 2) - depart(row 1)

What I am trying to calculate is the time between the sub-events within one transaction event.
So something like mvindex(2) -mvindex(1) but with iteration for all the sub events.
ie. something like mvindex(n) -mvindex(n-1)

I am not sure if I am making this more complex then it is

[cmerriman]

you'll probably want to do a streamstats command before the transaction or something. or a mvexpand after the transaction followed by a streamstats. transaction can be really tricky to work with when trying to get granular and i try to avoid it if i can.

[niketn]

@srichansen... the query that returns you multiple values. Can it be further granulated through any key field so that it only returns whatever value you have at index 0 and 1(preferably filter in the base search itself)?

[srichansen]

I think I can use the stopordernumber, the order of stops in a trip and streamstats to get a result.
I will give it a try and let you know.

Thanks for the advice.