Subtracting two epoch times after within stats table

[jason_hotchkiss]

Hello - we are trying to calculate the possible_duration between the first event and last event in the following base search:

<base_search>
| eval docid="https://www.youtube.com/embed/".docid
| stats count as "visits" values(docid) as url list(_time) as time_of_events earliest(_time) as first_event latest(_time) as last_event by user
| eval duration = last_event - first_event
| eval possible_duration = strftime(duration,"%H:%M:%S")
| eval time_of_events = strftime(time_of_events,"%H:%M:%S")
| eval first_event = strftime(first_event,"%H:%M:%S")
| eval last_event = strftime(last_event,"%H:%M:%S")
| table user visits url time_of_events first_event last_event possible_duration

Result:

Scoobie_Doo

3

https://www.youtube.com/embed/scoobie_snacks

16:12:37 16:12:37 16:12:34

16:12:34

16:12:37

19:00:03

The possible_duration field seems to get the minutes and seconds right.  But not the hour.  Looking for a suggestion one what I am missing.

[bowesmana · Accepted solution]

Using strftime on a duration type field will always take account your local time, so if you added in a date to that strftime, you would see it's probably Jan 1 1970.

When dealing with duration there are two ways, either using tostring or doing the maths, like this example search

| makeresults
| eval duration=147
| eval t-UsingToString=tostring(duration,"duration")
| eval h=round(duration/3600), m=round((duration-(h*3600))/60), s=duration%60
| eval t-UsingHMS=printf("%02d:%02d:%02d", h, m, s)
| table duration t-UsingToString t-UsingHMS

[jason_hotchkiss]

I attempted your solution, however, I am unable to get it to work in my particular case.

[bowesmana]

Can you give more details on what you tried and the results you got.

[jason_hotchkiss]

It ended up being my fault.  I was able to use an element of your example to produce the results I was looking for:

|eval duration = last_event - first_event
|eval possible_duration=tostring(duration, "duration")