How can I calculate the date difference by two timestamp?

[sakuraWu1]

Hi all,

I want to calculate the difference between dates within two different dates, my search is as below:

code 1:

| tstats min(_time) as first_time, max(_time) as last_time from datamodel=A by A.tag
| eval diff_date= (last_time- first_time)/ 86400

The result of code 1 can get some value, but it is not 100% accurate, for example, if my first_time is 10/23/2017 11:00pm and last_time is 10/24/2017 09:00am, the result I want is 1 day difference, I only focus on the date, but if I use above code, the date difference could be 0, cause it is within 24 hours.

code 2:

| tstats min(_time) as first_time, max(_time) as last_time from datamodel=A by A.tag
| eval first_date = strptime(first_time,"%Y-%m-%d") 
| eval last_date = strptime(last_time,"%Y-%m-%d") 
| eval diff = tostring((last_date-first_date), "duration")

I did a lot of research and someone mentioned that tostring function can also help. But my problem here is that I can't know why I can't get anything if I use strptime function, but I CAN get the correct date by using strftime function, however, still can't get anything for the diff column.

Can anyone Kindly help me on this? I really getting crazy trying to figure out this problem.

Thanks very much.

S

[Sukisen1981]

Code 1 eval first_date = strftime(first_time,"%Y-%m-%d-%H") | eval last_date = strftime(last_time,"%Y-%m-%d-%H")|eval firstdate = strptime(first_date,"%Y-%m-%d")|eval lastdate = strptime(last_date,"%Y-%m-%d-%H")|eval diff_date=(lastdate-firstdate)/86400 | fields diff_date

You can shorten the query of course, but remove |fields to see all fields and the calculations