I think there are a couple answers here, if you just want to do this, you can do it with a count and a HAVING clause like so:
SELECT time_bucket('1 hour', time) as bucket, avg(foo), count(foo)
FROM bar
WHERE id = 'x'
GROUP BY bucket
HAVING count(foo) = 60;
But that leaves the question of why you want to do this. Is it because you might be missing data? If it’s only a few points it might not matter for the average, but you can also use a time weighted average , which will account for measurements being valid for more time if you miss messages and if you use ‘Linear’ weighting you can linearly interpolate between the measurements you have.
So that’d look something like this:
SELECT
time_bucket('1 hour', time) as bucket,
time_weight('Linear', time, foo) -> average() as time_wt_avg
FROM bar
WHERE id = 'x'
GROUP BY bucket;
I’d worry more about missing points if you are trying to do say, the sum of the points and so if you don’t have all 60 with a normal sum the value would be way off and you couldn’t really compare between different buckets well. For that, I’d use the integral accessor function of time_weight and that will give you something that’s comparable even if you’re missing a few data points:
SELECT
time_bucket('1 hour', time) as bucket,
time_weight('Linear', time, foo) -> average() as time_wt_avg,
time_weight('Linear', time, foo) -> toolkit_experimental.integral('hour') as hourly_sum
FROM bar
WHERE id = 'x'
GROUP BY bucket;
So anyway, there are a couple options for you! Let me know if that helps!
PS You can still use the HAVING clause to say that you should avoid samples where you’re missing really big sections of data. Say if count(foo) < 30 or something.
If you want to get really fancy, you could use our new heartbeat_agg for that as well to see if there are any sections that don’t have data for a period longer than say 5 or 10 minutes. Timescale Documentation | heartbeat_agg()