Duration calculated incorrectly

[deepak02]

Hi,

My logs have the start and end time of an event. I am using the below query to calculate duration,

<filter terms> 
| eval epochStartDateTime=strptime(startDate." ".startTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval epochEndDateTime=strptime(endDate." ".endTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval duration=epochEndDateTime-epochStartDateTime

This works for all cases except the below,

startDate: 14/Jun/2017 startTime: 11:57:27.164
endDate: 15/Jun/2017 endTime: 12:00:48.401

Duration is returned in negative (duration=-42998.763000).
How do I handle this case?

Thanks,
Deepak

[DalJeanis]

I believe that your actual test data was 15/Jun/2017 instead of 14/Jun/2017. That correctly calculates to the numbers you posted, due to the %I hour , which means that "12:00:48.401" is roughly 12 hours before "11:57:27.164", or about -42998 seconds.

| makeresults
| eval startDate="15/Jun/2017", startTime="11:57:27.164"
| eval endDate="15/Jun/2017", endTime="12:00:48.401"
| eval epochStartDateTime=strptime(startDate." ".startTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval epochEndDateTime=strptime(endDate." ".endTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval duration=epochEndDateTime-epochStartDateTime

If you change the format string to "%d/%b/%Y %H:%M:%S.%3Q", to fix the issue pointed out by @davebrooking, then the calculation will change to 24-hour basis, and will calculate about 200 seconds duration.

[davebrooking]

I don't see a negative number when I try the calculation. However, one thing is puzzling me, %I is the hour in a 12 hour clock notation. To uniquely identify the hour wouldn't you also require the AM/PM portion of the timestamp.

[horsefez]

Hi,

unfortunately I can't provide you with a working solution to this, but I would like to suggest a command I've learned about few days ago.

It's the fieldformat command and it could help you when it comes around working and calculating with time and stuff.

Here is a link:
http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Fieldformat

[somesoni2]

The format of value of startDate in your question and in strptime do not match (14 June vs %d/%b/%Y). Is that a typo in your question?