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!

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...

The Visibility Gap: Hybrid Networks and IT Services

The most forward thinking enterprises among us see their network as much more than infrastructure – it's their ...

Get Operational Insights Quickly with Natural Language on the Splunk Platform

In today’s fast-paced digital world, turning data into actionable insights is essential for success. With ...