Splunk Search

How to get the difference between 2 date fields?

ajdyer2000
Path Finder

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 ?? ??
Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

ajdyer2000
Path Finder

thank you 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

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.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Strengthen Your Future: A Look Back at Splunk 10 Innovations and .conf25 Highlights!

The Big One: Splunk 10 is Here!  The moment many of you have been waiting for has arrived! We are thrilled to ...

Now Offering the AI Assistant Usage Dashboard in Cloud Monitoring Console

Today, we’re excited to announce the release of a brand new AI assistant usage dashboard in Cloud Monitoring ...

Stay Connected: Your Guide to October Tech Talks, Office Hours, and Webinars!

What are Community Office Hours? Community Office Hours is an interactive 60-minute Zoom series where ...