Average on time only, without considering date

[andreafebbo]

I have this query:

 Base quey
| eval EndTime = if(Result="OK", _time, null)
| eval StartTime = if(LogType ="START", _time, null)
| stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime by PackageName ExecutionInstanceGUID
| eval Duration = (EndTime-StartTime)
| where (Duration != "" OR Duration >= 0)
| stats avg(EndTime) as AVGEndTime avg(Duration) as AVGDuration avg(StartTime) as AVGStartTime by PackageName
| fieldformat AVGStartTime = strftime(AVGStartTime,"%H:%M:%S") 
| fieldformat AVGEndTime = strftime(AVGEndTime,"%H:%M:%S") 

My problems is that in calculating the average on AVGStartTime and AVGEndTime he considers the day also.
I'd like the average to be made just on the hour, minutes, seconds, not on the day.

Thanks.

[somesoni2 · Accepted solution]

Try this

Base quey
 | eval EndTime = if(Result="OK", _time-relative_time(_time,"@d"), null)
 | eval StartTime = if(LogType ="START", _time-relative_time(_time,"@d"), null)
 | stats Latest(StartTime) as StartTime Latest(EndTime) as EndTime by PackageName ExecutionInstanceGUID
 | eval Duration = (EndTime-StartTime)
 | where (Duration != "" OR Duration >= 0)
 | stats avg(EndTime) as AVGEndTime avg(Duration) as AVGDuration avg(StartTime) as AVGStartTime by PackageName
 | fieldformat AVGStartTime = strftime(AVGStartTime,"%H:%M:%S") 
 | fieldformat AVGEndTime = strftime(AVGEndTime,"%H:%M:%S") 

[woodcock]

Show the output as it is now and then show the desired output. I do not understand "the problem" and how "he considers the day" because that part is not in the search. Show us the entire search.