topic Re: How to find the difference between dates only counting business hours? in Splunk Search

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

karthikTIL — Mon, 28 Sep 2020 17:31:31 GMT

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.

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

MuS — Tue, 09 Sep 2014 05:28:47 GMT

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

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

karthikTIL — Tue, 09 Sep 2014 07:42:51 GMT

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

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

MuS — Mon, 28 Sep 2020 17:31:48 GMT

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.

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

karthikTIL — Mon, 28 Sep 2020 17:32:23 GMT

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.

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

MuS — Wed, 10 Sep 2014 09:56:14 GMT

see my update

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

karthikTIL — Mon, 28 Sep 2020 17:33:06 GMT

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?

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

karthikTIL — Thu, 18 Sep 2014 06:57:06 GMT

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

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

MuS — Thu, 18 Sep 2014 08:11:09 GMT

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