Splunk Search

## How do I calculate the date difference for two timestamps other than _time and exclude weekends?

Explorer

Hello,
I have two timestamps , both are NOT `_time`.

2. Processed Date - 09/14/16

I need to calculate the age of these two, but need to exclude weekends. I need something like below

``````base search | eval age = (Processed Date - Received date). | table age
``````

In the above example the result should be 2, so that weekend is excluded..
It should not be 4.

I think `date_wday` excludes days from `_time` only. But for me the timestamp for calculating the age is not `_time`.

Tags (5)
1 Solution
Revered Legend

Try something like this (run anywhere sample, replace first line with your current search.

``````| gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016"
| eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) | convert ctime(dates)  timeformat="%A" | eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) | eval noOfDays=mvcount(dates)
``````
Revered Legend

Try something like this (run anywhere sample, replace first line with your current search.

``````| gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016"
| eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) | convert ctime(dates)  timeformat="%A" | eval dates=mvfilter(NOT match(dates,"(Saturday|Sunday)")) | eval noOfDays=mvcount(dates)
``````
Explorer

Thanks so much. excellent.. this worked for me.. BTW, i didnt use gentimes start=-1, is it mandatory?

And how to exclude public holidays...thanks again for your help

Revered Legend

This line is to generate a sample data to show the remaining processing steps. The whole thing should be replaced by your search which get the required data.

``````| gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016"
``````

There is no in-build way to identify public holidays in Splunk, you would need to create some custom lookup with dates which are publich holidays and exclude them (not straightforward way. Don't have a ready to use solution, will need to figure that one out).

Motivator

@somesoni2 you're solution is fascinating. I don't use the mvrange or mvcount much. Can you explain what is happening in the eval `| eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400)` please

Revered Legend

It's basically creating a multivalued field dates with arithmetic series starting with epoch value of Receivedate, ending at epoch value of Processdata, with step of 86400 (1 day in secs). I think it would be clearer if you can run this query

`````` | gentimes start=-1 | eval Processdate="9/14/2016" | table Processdate| eval Receivedate="9/10/2016" | eval dates_epoch=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400)
| convert ctime(dates_epoch) as dates_human
``````
New Member

@somesoni2 : Do you have the solution to identify holidays yet ?
Thanks.

Revered Legend

I think so. As I mentioned before, for excluding public holidays, you'd need to setup a lookup table with list of holidays. You can find one here: https://gist.github.com/shivaas/4758439

Once you've your lookup table setup, say holidays.csv with column date (holiday date in format `%Y-%m-%d`,name (don't really care for this column), your query would be like this (using a sample data):

``````| gentimes start=-1 | eval Processdate="06/01/2018" | table Processdate| eval Receivedate="05/01/2018"
| eval dates=mvrange(strptime(Receivedate,"%m/%d/%Y"),strptime(Processdate,"%m/%d/%Y"),86400) | convert ctime(dates) timeformat="%A %Y-%m-%d" | eval workdays=mvfilter(NOT match(dates,[| inputlookup holidays.csv | stats values(date) as date| eval date=mvappend("(Saturday",date,"Sunday)")| eval search=mvjoin(date,"|") | table search | format "" "" "" "" "" ""])) | eval noOfDays=mvcount(dates)
``````
New Member

Hi,

Getting the below error :

Error in 'eval' command: The expression is malformed. An unexpected character is reached at ') ))'.

Also, the last part, shouldn't it be mvcount(workdays) instead of mvcount(dates) ?

Get Updates on the Splunk Community!

#### User Groups | Upcoming Events!

If by chance you weren't already aware, the Splunk Community is host to numerous User Groups, organized ...

#### Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

#### Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...