Splunk Search

## How to find the difference between dates only counting business hours?

Path Finder

HI,I have two fields A and B with time format as 1/07/2014 3:41:12 PM.
e.g., if A is 1/07/2014 3:41:12 PM and B is 15/07/2014 2:41:12 PM.
To find difference i know we need to use,
"eval epoch_A=strptime('Field A',"%d/%m/%Y %I:%M:%S %p") | eval epoch_B=strptime('Field B',"%d/%m/%Y %I:%M:%S %p") | eval diff=round(('epoch_B'-'epoch_A') / 3600)|table diff"

Please let me know how to find difference in hours, taking into account only business hours i.e., from 9AM to 5PM and excluding Saturday,Sunday,Public Holiday.

Tags (3)
SplunkTrust

okay, hard to test without any real data ... try this:

`````` source="file.csv" date_month=august date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday )
| eval epoch_A=strptime('start_TIME',"%d/%m/%Y %I:%M:%S %p")
| eval epoch_B=strptime('close_TIME',"%d/%m/%Y %I:%M:%S %p")
| eval total_TIME=if((epoch_B-epochA)>57600, (epoch_B-epcoh_A-57600)/3600, (epoch_B-epcoh_A)/3600 )
| table start_TIME, close_TIME, total_TIME
``````

The `if` statement is based on the fact, that if the difference between start and end time is bigger as 57600 seconds (17 hours or 5pm to 9am) then this job was running over night and you simply minus those 17 hours from the total time.

hope this helps ... and this is un-tested 😉

cheers, MuS

SplunkTrust

Hi karthikTIL,

Each event has a field called `date_hour` which has the hour of the day as value, maybe this is of help in your case.
Something like this just to give an example on how to use `date_hour`:

``````Your base search date_hour>08 OR date_hour<18 | your next cammond
``````

UPDATE:

Basically you can just add the `date_hour` and `date_wday` to the base search like this :

``````source="file.csv" date_month=august date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday )
| bucket _time span=1d
| eval epoch_A=strptime('start_TIME',"%d/%m/%Y %I:%M:%S %p")
| eval epoch_B=strptime('close_TIME',"%d/%m/%Y %I:%M:%S %p")
| eval total_TIME=(('epoch_B'-'epoch_A') / 3600)
| table start_TIME,close_TIME,total_TIME
``````

This way you will only get back results for this time range ( 9am to 5pm ) and only on weekdays Mo - Fr. You can decide if you need the `bucket` or not, this will `group` all events into per day slices, which will be used in `timechart` or `chart` for example.

For the public holidays you would have to use a lookup.

Cheers, MuS

Path Finder

Sorry, it did not work.
If start_time = 26/08/2014 3:23:29 PM and close_time=27/08/2014 12:15:18 PM , your query gives me total_time=20.86 hours, whereas i expect close_time = 4.52hours.
Not sure if converting epoch time is culprit here.Or am i doing something wrong here?

Path Finder

HI MuS, is there any update you want to provide please

SplunkTrust

see my update

Path Finder

HI MuS, Thank you. i got your point but don't know how exactly to include your statement in my query,Could you please le tme know.

My current query is,
source="file.csv" date_month=august|eval epoch_A=strptime('start_TIME',"%d/%m/%Y %I:%M:%S %p") | eval epoch_B=strptime('close_TIME',"%d/%m/%Y %I:%M:%S %p") | eval total_TIME=(('epoch_B'-'epoch_A') / 3600)|table start_TIME,close_TIME,total_TIME

It converts close_time and start_time into epoch time and find the difference(24 hours day).Please let me know how to include your statement above to get total working hours.

SplunkTrust

Not exactly. `date_hour` starts at 0 (midnight to 1am) and ends with 23 (11pm to midnight). So using date_hour>8 starts at 9am and date_hour<18 ends at 5pm.

use this run everywhere command:

``````index=_internal date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday ) series | bucket _time span=1d | streamstats first(date_hour) AS first_hour, last(date_hour) AS last_hour by series,_time | eval diff=first_hour-last_hour | chart span=1d values(diff) AS diff over _time by series
``````

this will calculate the diff for first hour to the last hour seen each day per series.

Path Finder

HI MuS, I guess the above will give events which started between 8 and 18 hrs. what i am after is,when i calculate difference between close time and start time, i need to consider only from 9 AM to 5 PM

e.g., start time=8/09/2014 9AM; end time= 9/09/2014 10AM
when i do end time- start time, it should be 9 hrs

Get Updates on the Splunk Community!

#### Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

#### Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

#### We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...