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

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...