How do I find the time difference between these events?

[dhavamanis]

We have the events like below (fields like flowId, action..etc) and need a final output between the events (action = FLOW_END_SUCCESS and FLOW_START) time difference based on flowId,

2016-05-18 21:33:10,208 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607190206, flow=INTAKE, type=player, action=FLOW_END_SUCCESS, mpxId=116310, accountId=46862
2016-05-18 21:33:10,208 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607190206, flow=INTAKE, type=player, action=SAVE_ITEM_TO_DB_END, mpxId=116310, accountId=46862
2016-05-18 21:33:10,207 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607190206, flow=INTAKE, type=player, action=SAVE_ITEM_TO_DB_START, mpxId=116310, accountId=46862
2016-05-18 21:33:10,206 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607190206, flow=INTAKE, type=player, action=FLOW_START, mpxId=116310, accountId=46862
2016-05-18 21:33:05,269 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607185173, flow=INTAKE, type=player, action=FLOW_END_SUCCESS, mpxId=116310, accountId=46857
2016-05-18 21:33:05,269 INFO [pool-3-thread-1] c.n.m.l.EventLogger [EventLogger.java:18] env=DEV, flowId=1463607185173, flow=INTAKE, type=player, action=SEND_ITEM_TO_QUEUE_END_SUCCESS, mpxId=116310, accountId=46857

Final output:

flowId,    time_difference_in_ms
1463607185173,   1234
1463607190206,   5678

[sundareshr · Accepted solution]

See if this gives you what you're looking for

| stats values(eval(if(action="FLOW_START", _time, null()))) AS start values(eval(if(action="FLOW_END_SUCCESS", _time, null()))) AS end by flowId | eval dur=tostring(end-start, "duration")

[dhavamanis]

Can you provide the query to get the duration in milliseconds . currently its showing like "00:00:00.094"