Archive

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