Splunk Search

How to calculate the number of days between two dates?

twh1
Communicator

I have two dates as part of a string. I have to get these dates in separate fields by using the substr function. Now, I want to calculate the number of days difference between those two dates.

| base search
| eval date1=substr(HIGH_VALUE, 10, 19) 
| eval date2=substr(PREV_HIGH_VALUE, 10, 19) 
| eval it = strptime(date1, "%Y-%m-%d %H:%M:%S") 
| eval ot = strptime(date2, "%Y-%m-%d %H:%M:%S") 
| eval diff = (it - ot) | eval date_diff=strftime(diff,"%Y-%m-%d %H:%M:%S")

I am unable to get the desired result by the above query.

I have to calculate a new field data based on number of difference between two dates.

Tags (3)
0 Karma
1 Solution

kmaron
Motivator

try this:

| base search
| eval date1=substr(HIGH_VALUE, 10, 19) 
| eval date2=substr(PREV_HIGH_VALUE, 10, 19) 
| eval it = strptime(date1, "%Y-%m-%d %H:%M:%S") 
| eval ot = strptime(date2, "%Y-%m-%d %H:%M:%S") 
| eval daysdiff=round((ot-it)/86400,0)

View solution in original post

0 Karma

kmaron
Motivator

try this:

| base search
| eval date1=substr(HIGH_VALUE, 10, 19) 
| eval date2=substr(PREV_HIGH_VALUE, 10, 19) 
| eval it = strptime(date1, "%Y-%m-%d %H:%M:%S") 
| eval ot = strptime(date2, "%Y-%m-%d %H:%M:%S") 
| eval daysdiff=round((ot-it)/86400,0)
0 Karma

twh1
Communicator

Hi @kmaron above answer help me getting the days difference. Could you help me to count hour difference if daysdiff is less than 1.

0 Karma

kmaron
Motivator

The 86400 is the number of seconds in a day. So if you want hours instead just use 3600 instead.

 | eval hoursdiff=round((ot-it)/3600,0)

you could do some math in order to get days and hours so you always have both.

 | eval daysdiff=round((ot-it)/86400,0)
 | eval hoursleft=(round((ot-it)/3600,0)-(daysdiff*24))
0 Karma

harsmarvania57
Ultra Champion

Hi @twh1,

Try below query

| base search
| eval date1=substr(HIGH_VALUE, 10, 19) 
| eval date2=substr(PREV_HIGH_VALUE, 10, 19) 
| eval it = strptime(date1, "%Y-%m-%d %H:%M:%S") 
| eval ot = strptime(date2, "%Y-%m-%d %H:%M:%S") 
| eval diff = (it - ot)
| eval daysBetween=round(diff/86400,2)
0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...