How to calculate time difference?

[EvansB]

I'm looking to get a difference between both times and create a 3rd field for the results (Properties.actionedDate - _time). My current query is like this

index=* source=* | table Properties.actionedDate, _time

Here is a screenshot of my current result

[vishenps]

Hi @richgalloway, @ITWhisperer  I have similar doubt but little tedious: 


Use case:In my org primarily Mission Control events are investigated by SOC as soon as they pop up, if futher investigation is needed the incident is escalated to Enterprise security TEAM who is responsible to perform deeper/detailed investigation and update back in Mission Control. 
USE CASE: 
The enterprise security manger wants a DASHBOARD which will inform him about : 
if the investigation is being performed by his team (ES)> how much average time his team member takes to resolve an incident (for now I'm only focusing on this)> averaged over a month. 

jeff is ES resource  & stephen is SOC resource 
i want to pick end_time where resource is Stephen and notes is "Escalation to ES" and start_time where resource is jeff and subtract them in order to get claim_time_by_ES. 

SO far the query I'm using but not successful yet is: 

| mcincidents unwind_to=task 
| search incident_id="3e864839-xyzab"
| eval is_es_team=if(IN(owner, "Jeff","Rama", "Mel"), 1, 0) 
| eval is_soc_team=if(IN(owner, "Stephen", "Crossman", "Ruby","Cole"), 1,0)
| eval end_time_for_soc=if(is_soc_team==1 AND name=="Escalation to ES", end_time, null()) 
| eval start_time_for_ES=if(is_es_team==1, start_time, null()) 
| eval total_time_claimed=end_time_for_soc - start_time_for_ES

in the below snapshot of log the columns name are in sequence of:  owner > start_time > end_time > total_time_taken> notes

[richgalloway · Accepted solution]

I probably should make this a macro since I give this answer a lot.  Timestamps must be in epoch (integer) format to be compared or to find their difference.  Use the strptime function for that.

index=* source=* 
| rename Properties.actionedDate as actionedDate
| eval actionedTS = strptime(, "%Y-%m-%dT%H:%M:%S.%7N%:z")
| eval diff = _time - actionedTS
| table actionedDate, _time, diff

[EvansB]

Thanks for your response.
This should work but I'm not getting results on the diff field - (I sorted with this field)
Does that mean there are no difference between timestamps and _time?

[richgalloway]

I noticed a typo in the strptime format string in my reply, which I've corrected.  I also added a rename command in case eval doesn't like the original field name.

[EvansB]

did you edit your first comment.... I'm getting same results 
can you post the corrected query? 
Appreciate.

[richgalloway]

I don't know what happened to my original edit, but I've re-posted it.

[EvansB]

I eventually used this

index=* source=*
| rename Properties.actionedDate as actionedDate
| eval actionedTS = strptime(actionedDate, "%Y-%m-%dT%H:%M:%S.%7N%:z")
| eval diff = _time - actionedTS 
| table actionedDate, _time, diff

Appreciate you @richgalloway