Splunk Search

How can I calculate the number of days between now() and a transaction date?

rfernandez2010
New Member

Hello Splunkers,

How would I be able to calculate the number of days between todays days which I'm using the now() function, and the date stored the transaction accorded? The way transaction dates appear on our system is in a (mmddYYYY) format so for example 02052018.

I'm trying to use strfttime(OPEN_DATE,"%m,%d,$Y") but it converts every date into a calendar date ending in 1970.

0 Karma
1 Solution

jkat54
SplunkTrust
SplunkTrust

try this in your search:

| eval days=(now()-strptime(OPEN_DATE, "%m%d%Y"))/86400

now() is epoch time (seconds since Jan 1st 1970 GMT)
strptime(OPEN_DATE, "%m%d%Y") should give you seconds since Jan 1st 1970 GMT for OPEN_DATE

now() - strptime(OPEN_DATE, "%m%d%Y") = difference in epoch times for now and OPEN_DATE

Divided by 86400 gives you the difference in days. You might want to round it too:

| eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400,2)

View solution in original post

493669
Super Champion

try this:

|makeresults|eval starttime="02042018" |eval time=strptime(starttime,"%m%d%Y")|eval days=floor((now()-time)/86400)
0 Karma

micahkemp
Champion
| eval seconds_since_open_date=now()-strptime(OPEN_DATE, "%m%d%Y"), days_since_open_date=seconds_since_open_date/86400

It looks like you were using strftime (format) instead of strptime (parse), and you also had commas in your format string, but had none in your sample date string.

jkat54
SplunkTrust
SplunkTrust

try this in your search:

| eval days=(now()-strptime(OPEN_DATE, "%m%d%Y"))/86400

now() is epoch time (seconds since Jan 1st 1970 GMT)
strptime(OPEN_DATE, "%m%d%Y") should give you seconds since Jan 1st 1970 GMT for OPEN_DATE

now() - strptime(OPEN_DATE, "%m%d%Y") = difference in epoch times for now and OPEN_DATE

Divided by 86400 gives you the difference in days. You might want to round it too:

| eval days=round((now()-strptime(OPEN_DATE, "%m%d%Y"))/86400,2)

rfernandez2010
New Member

Thank you, now I'm getting the answers I need. Nice addition of the round function too.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...