How to get the difference (In whole days) between two fields within the same event

[Matthew]

Hi Guys, 

Wondering if you can help me out with the following. Within a single event I have to fields: 

1) expiry_date

2) delivery_date

I would create a new variable giving me the difference in days between the delivery date and the expiry date for each order. 

For example 

expiry_date=2021-05-25T00:00:00Z

delivery_date=2021-04-27T19:00:44Z

Should give a result of 28days. 

Hope you can help! Thanks in advance. 

[harsmarvania57 · Accepted solution]

Here you go

<your_query>
| eval diff=tostring(strptime(expiry_date, "%Y-%m-%dT%H:%M:%S%Z")-strptime(delivery_date, "%Y-%m-%dT%H:%M:%S%Z"), "duration")

Sample query which I ran

| makeresults 
| eval expiry_date="2021-05-25T00:00:00Z", delivery_date="2021-04-27T19:00:44Z"
| eval diff=tostring(strptime(expiry_date, "%Y-%m-%dT%H:%M:%S%Z")-strptime(delivery_date, "%Y-%m-%dT%H:%M:%S%Z"), "duration")

[Matthew]

Hi harsmarvania57, 

Worked like a charm. Thank you!