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 | ?? | ?? |
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.
thank you
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
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.