How to calculate the number of days between two dates?

[twh1]

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.

[kmaron · Accepted solution]

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)

[twh1]

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

[kmaron]

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

[harsmarvania57]

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)