Splunk Search

How to calculate the number of days between two dates?

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

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

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

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

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

SplunkTrust
SplunkTrust

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!