How to get data by _time

[Learner]

Hi everyone,

index=xyz source="something"
|stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId
| eval duration = maxTime - minTime 
| stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime"

If I run this script for a day  then I get some non-zero value for AvgResponseTime and P95ResponseTime also get different values for minTime and maxTime.

But if i run following script for same time, I get same values for minTime  and maxTime.

index=xyz source="something" 
|bin _time span=1d
|stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId _time
| eval duration = maxTime - minTime 
|| eval Time=strftime(_time , "%d/%m/%Y %H:%M") 
| stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime" by Time

At the end I get 0 for AvgResponseTime and P95ResponseTime which is not matching with above query as value of duration gets as 0.

I want output of based on each day AvgResponseTime and P95ResponseTime 

I hope I give clear idea about my issue. What would I do to resolve this?

[ITWhisperer · Accepted solution]

bin will set all the times to the beginning of the day, so when you do stats by the same field, they will all be the same - hence 0 duration. Use bin to set up a different field (day) and do stats by that, thus leaving _time undisturbed

index=xyz source="something" 
|bin _time span=1d as day
|stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId day
| eval duration = maxTime - minTime 
|| eval Time=strftime(day, "%d/%m/%Y %H:%M") 
| stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime" by Time

[Learner]

thank you very much @ITWhisperer . it solved some part of my problem. i checked the values for "AvgResponseTime" and "P95ResponseTime" and they are not matching. Suppose i ran my code without filtering _time, i get values as 0.4 and 1.7 to "AvgResponseTime" and "P95ResponseTime" respectively. but if i use your code i get average values of  "AvgResponseTime" and "P95ResponseTime"  as 0.4 and 1.9 respectively. why is that so? and how to solve this?

thanks, in advance!

[ITWhisperer]

What do your two searches look like?

[Learner]

Query which I specified, is my actual query.