Getting Data In

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

karthikTIL
Path Finder

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

1 Solution

tom_frotscher
Builder

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)

View solution in original post

karthikTIL
Path Finder

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?

0 Karma

tom_frotscher
Builder

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
Path Finder

Excellent,Thanks Tom

0 Karma

tom_frotscher
Builder

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
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...