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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...