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
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
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
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)
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")