Getting Data In

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

Makinde
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
1 Solution

javiergn
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

javiergn
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

Makinde
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

javiergn
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...