Getting Data In
Highlighted

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

New Member

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

Highlighted

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

New Member

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)

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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