Splunk Search

How can I calculate the date difference by two timestamp?

sakuraWu1
New Member

Hi all,

I want to calculate the difference between dates within two different dates, my search is as below:

code 1:

| tstats min(_time) as first_time, max(_time) as last_time from datamodel=A by A.tag
| eval diff_date= (last_time- first_time)/ 86400

The result of code 1 can get some value, but it is not 100% accurate, for example, if my first_time is 10/23/2017 11:00pm and last_time is 10/24/2017 09:00am, the result I want is 1 day difference, I only focus on the date, but if I use above code, the date difference could be 0, cause it is within 24 hours.

code 2:

| tstats min(_time) as first_time, max(_time) as last_time from datamodel=A by A.tag
| eval first_date = strptime(first_time,"%Y-%m-%d") 
| eval last_date = strptime(last_time,"%Y-%m-%d") 
| eval diff = tostring((last_date-first_date), "duration")

I did a lot of research and someone mentioned that tostring function can also help. But my problem here is that I can't know why I can't get anything if I use strptime function, but I CAN get the correct date by using strftime function, however, still can't get anything for the diff column.

Can anyone Kindly help me on this? I really getting crazy trying to figure out this problem.

Thanks very much.

S

0 Karma

Sukisen1981
Champion

Code 1 eval first_date = strftime(first_time,"%Y-%m-%d-%H") | eval last_date = strftime(last_time,"%Y-%m-%d-%H")|eval firstdate = strptime(first_date,"%Y-%m-%d")|eval lastdate = strptime(last_date,"%Y-%m-%d-%H")|eval diff_date=(lastdate-firstdate)/86400 | fields diff_date

You can shorten the query of course, but remove |fields to see all fields and the calculations

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 ...