Hi, I’ve written an influxql query to calculate http availability percentage. It’s actually very simple and it works as I expect it to be except for one detail.
SELECT count("http_response_code") /15 AS "availability"
FROM "http_response"
WHERE ("name" =~ /^$http_name$/ AND "status_code" = '200' AND "result" = 'success')
AND (time > now()-7d)
GROUP BY time(5m), "name" fill(null)
Basically it counts every successful response and divides it to total requests in that time bucket. (15 in this case, because the query is grouped by 5m and telegraf sends an http request each 20s. So, 15 requests in 5m).
The problem is, when we run this query for example at 11:42 (2 minutes passed not 5m, and we grouped by 5m), it divides number of successes / 15 which is true for 5m but not for 2m. So, the last data point is an incorrect calculation since the data is incomplete.
So, I want to remove the last data point. I thought I can do that by adding a WHERE clause.
WHERE time < now()-5m
which of course doesn’t work since it goes from 11:42 to 11:37 and now I’m still at 3/5 of the time. Is there a way to round this time? Drop the incomplete last data point?
I could group by 1m to approximate better but it costs computation time. I even want to go with higher grouping intervals.
