How to find the difference between 2 times in date time format?

[karthikTIL]

HI, I have two fields A and B with time format as 1/07/2014 3:41:12 PM.
Please let me know how to find difference between A and B in hours with this format?

e.g., if A is 1/07/2014 3:41:12 PM and B is 2/07/2014 2:41:12 PM, B-A should be 23 hours

[karthikTIL]

HI, is there a way in splunk to get the difference only on the business hours i.e., from 9AM to 5PM and excluding saturday,sunday,public holidays?

[tom_frotscher · Accepted solution]

Hi,

you could convert your two timestamps to epoch time, which is then seconds. Then you can calculate the difference between your timestamps in seconds (your B-A). After this you divide the result by 3600 which is an hour in seconds. If you want, you can then round to a full hour or whatever you like:

| eval epoch_A=strptime(A,"%d/%m/%Y %I:%M:%S %p") | eval epoch_B=strptime(B,"%d/%m/%Y %I:%M:%S %p") | eval diff=round(('epoch_B'-'epoch_A') / 3600)

[karthikTIL]

Excellent,Thanks Tom

[tom_frotscher]

Try to replace the " with ' symbols for the first argument in the strptime function. Like this:

"eval epoch_A=strptime('Assigned_TIME',"%d/%m/%Y %I:%M:%S %p") | eval epoch_B=strptime('In Progress_TIME',"%d/%m/%Y %I:%M:%S %p") | eval diff=round(('epoch_B'-'epoch_A') / 3600)|table diff"

[karthikTIL]

I dont find output when i mention this
"eval epoch_A=strptime("Assigned_TIME","%d/%m/%Y %I:%M:%S %p") | eval epoch_B=strptime("In Progress_TIME","%d/%m/%Y %I:%M:%S %p") | eval diff=round(('epoch_B'-'epoch_A') / 3600)|table diff"

fyi., A is Assigned_Time and B is In Progress_Time