Why my search adding an additional day when finding the time difference between two time fields?

[jpanderson]

I have two values in my events: "OccuredOn" (ignore the spelling...) and "EndTime". Quite simply, I want the difference between these two values. I've converted them to a date time format and simply subtracted one from the other, see below:

 generic search....
 | eval EndTimeTime = strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3Q")
 | eval OccurredOnTime = strptime(OccuredOn,"%Y-%m-%dT%H:%M:%S.%3Q")
 | eval ResponseTime = EndTimeTime-OccurredOnTime
 | eval ResponseTimeString = strftime(ResponseTime, "%d %H:%M:%S.%3Q")
 | eval HourOccurred = strftime(OccurredOnTime,"%Y-%m-%d %H")
 | table EndTime, EndTimeTime, OccuredOn, OccurredOnTime, ResponseTimeString

If I format these times just as Hour, Minute, Second, Microseconds, then it works fine. The hours, minutes and seconds are all correct. However, when I use a day, it incorrectly says there is a day's difference.

E.g. for the following record, I would expect the ResponseTimeString to be around 2 hours 17 minutes, as you can see it looks correct apart from the day. Why does it think there is a day difference?

EndTime - OccurredOnTime - ResponseTimeString
2016-01-31 23:28:07.991000 - 2016-01-31T21:10:09.45 - 01 02:17:58.541

I could always just omit the day value, but I'd rather leave it on for the unlikely event where I have a request which runs over an entire day, all other requests should look something like: 0 days, 04:10:11.12412

What is wrong here?

[richgalloway · Accepted solution]

This should do the job.

| eval EndTimeTime = strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval OccurredOnTime = strptime(OccuredOn,"%Y-%m-%dT%H:%M:%S.%3Q")
| eval ResponseTime = EndTimeTime-OccurredOnTime
| fieldformat ResponseTime = tostring(ResponseTime, "duration")
| eval HourOccurred = strftime(OccurredOnTime,"%Y-%m-%d %H")
| table EndTime, EndTimeTime, OccuredOn, OccurredOnTime, ResponseTime

[jpanderson]

Seems to work in some situations but comes up with 10 hours 25 minutes when I find the difference between 00:00:32 and 13:35:21...
EDIT nevermind it works fine, I was looking at the wrong days

For now I've just stuck with the unformatted time which is in seconds. Thanks for the answer. I've marked this as the accepted answer though for its simplicity

[javiergn]

Hi,

When you format your response time as %d you are implicitly telling Splunk that your day is in a two-digit format ranging from 01 to 31. There's no 00.

Therefore you need a workaround I'm afraid. Try this:

| eval ResponseTimeString = floor(ResponseTime/86400) . " " . strftime(ResponseTime, "%H:%M:%S.%3Q")

[jpanderson]

Seems to work in some situations but comes up with 10 hours 25 minutes when I find the difference between 00:00:32 and 13:35:21...
EDIT nevermind it works fine, I was looking at the wrong days

For now I've just stuck with the unformatted time which is in seconds. Thanks for the answer

[javiergn]

That's because the difference between those two times is negative.
Have you tried with absolute values?

| eval ResponseTime = abs(EndTimeTime-OccurredOnTime)

It does work for me with your example.

[jpanderson]

I didn't realise one of the values was actually on another day, so the response time value made sense in the end.