Splunk Search

Time difference during mid night is showing as more than a day

gravi
Explorer

Hi,

I have two datetime stamps, both in same format ( %m-%d-%Y %H:%M:%S %p UTC ) and i am trying to get the difference of the two.

i am converting them to (%Y-%m-%d %I:%M:%S) and getting the difference. For the time during mid night say, the difference between 11:56 PM and 12:06 AM is showing as more than a day. How to I get the correct difference in tis scenario?

Current scenario:

Time-1:        12-6-2019 11:53:24 PM UTC
Converted T-1: 2019-12-06 11:53:24  
Time-2 :       12-7-2019 12:06:04 AM UTC    
Converted T-2: 2019-12-07 12:06:04  
Difference:    1+00:12:40.000000

Thanks

Tags (1)
0 Karma

FrankVl
Ultra Champion

I think the issue is with your initial conversion. You say you are using %m-%d-%Y %H:%M:%S %p UTC to interpret your original timestamps, that is incorrect. Your timestamps don't have 24h clock, they have 12h clock + AM/PM. When you use %H I bet the %p (AM/PM) gets ignored. Use %m-%d-%Y %I:%M:%S %p UTC

Also: it would help if you would post the actual search code that you are using, so we can see more accurately what you are doing.

0 Karma

Anantha123
Communicator

Hi,

Try this once

Let the time be in %H instead of %I format . then
| eval Duration = tostring((Time2 - Time1), "duration") | table Duration.

Thanks
Anantha.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

This is wrong.. You need to do the time difference in epoch format, as I suggested in my answer below

0 Karma

Anantha123
Communicator

@skoelpin : My syntax is not wrong. May be it did not worked in this scenario .It did work for me when I had to take the time difference.
when you assign time to a variable , it is automatically converts to epoch format. I believed Gravi was assigning different _time's to Time1 and Time2 variable.

Thanks
Anantha.

0 Karma

gravi
Explorer

It gives me the same result even if I use 24hr format %I

0 Karma

skoelpin
SplunkTrust
SplunkTrust

You need to convert it to epoch time first to do the calculation, then format it after the calculation. Like this

index=... 
| eval epoch_one=strptime(<FIELD_one>, "%m-%d-%Y %H:%M:%S %p UTC")
| eval epoch_two=strptime(<FIELD_two>, "%m-%d-%Y %H:%M:%S %p UTC")
| eval diff=epoch_one-epoch_two
| eval time_diff=diff/60
| timechart max(time_diff) AS time_diff
0 Karma

gravi
Explorer

Need the difference in minutes not the DateTimeformat

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Then calculate the time? I just showed you how....

See the updated answer

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...