How can I find the difference in days between two timestamps in this format?

[Makinde]

Hi,

I would like to find out the difference in days between two timestamps however the time format is a little weird.

This is the time format: 2016-03-19T15:05:40Z

[javiergn · Accepted solution]

Probably easier to explain with an example. See below:

| stats count
| eval t1_string = "2016-03-19T15:05:40Z"
| eval t2_string = "2016-03-20T16:06:41Z"
| eval t1 = strptime(t1_string, "%Y-%m-%dT%H:%M:%SZ")
| eval t2 = strptime(t2_string, "%Y-%m-%dT%H:%M:%SZ")
| eval diffInSecs = t2 - t1
| eval diffInDuration = tostring(t2 - t1 , "duration")
| table t1_string, t2_string, diff*

Output:

t1_string   t2_string   diffInDuration  diffInSecs
2016-03-19T15:05:40Z    2016-03-20T16:06:41Z    1+01:01:01.000000   90061.000000 

Let me know if that helps

[Makinde]

Thanks Javiergn

This seems to work, however how can round up the duration value to the nearest days so instead of 1+01:01:01 how can I round it off to one day and if larger than 12 hrs round it off 2 days

Secondly I would like to do a stats average of all the duration value, is that possible? something like this:

... search ... | stats avg(diffInDuration)

[javiergn]

For the first question I wouldn't use duration in that case, I would simply divide the difference in seconds by 86400 and then use round to round it to the closest integer value. For example:

your search here
| eval diffInSecs = t2 - t1
| eval days = round(diffInSecs/86400, 0)

You can't do a stats avg of duration because it is a string but you can do an average of the time in seconds and then present that as a duration. Hope that makes sense.

yoursearch
| stats avg(diffInSecs) as avgDiffInSecs
| eval avgDiffInDuration = tostring(avgDiffInSecs , "duration")