How to get the difference between 2 date fields?

[ajdyer2000]

Hi All

thank you all so much for helping me. this is a great forum to learn.

I have 2 date fields and I'd like to get the difference in days and if they are over certain thresholds <30 ,>30, >60, >120

For Example

Current-Date	Open-Date	Diff Days	Metric
4/25/2022	4/23/2022	2	<30
4/25/2022	3/15/2022	41	>30
4/25/2022	2/15/2022	69	>60
4/25/2022	12/25/2021	121	>120
4/25/2022	4/1/2022	??	??
4/25/2022	11/25/2021	??	??
4/25/2022	1/15/2022	??	??

[ajdyer2000]

thank you 

[PickleRick]

Main question here is what is the form of your dates. If they are unix timestamps, just substract one from another and you're home. If they're strings (as in your example), you have to first parse them to timestamps.

<your search>
| eval opentimestamp=strptime('Open-Date',"%m/%d/%Y")
| eval currenttimestamp=strptimr('Current-Date',"%m/%d/%Y")
| eval diffdays=(currenttimestamp-opentimestamp)/86400

[richgalloway · Accepted solution]

To compare dates they must first be in epoch (integer) format.  Use the strptime function to do that.  Then you can subtract to the difference.  The result will be in seconds so divide by 86400 to get days.  The Metric field is just a case statement.

| eval current_epoch=strptime('Current-Date', "%m/%d/%Y"), open_epoch=strptime('Open-Date', "%m/%d/%Y")
| eval diff=(current_epoch - open_epoch) / 86400
| eval Metric=case(diff<30,"<30", diff>120,">120", diff>60,">60", diff>30,">30")
| rename diff as "Diff Days"

BTW, try to avoid hyphens in field names as they just complicate the SPL.