[INFLUXQL] Drop the Last Data Point

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.

Hello @Mert,
I think the easiest solution is to figure out how many points you expect to return (n) with that query and to return (n-1) with the limit() function.

1 Like

Thanks @Anaisdg, it works. One drawback is that I have to make the time horizon (7d) fixed too.

If you were to use flux, you could write a function to calculate the n for you you and put the output into your query.

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.