Downsampling & Data Retention

Store
1

Hi, I’d be grateful for a little help please.

I have a sensor which provides data every 5 seconds, and my goal is to have the previous 1 hour of data at 5 second precision, and older data downsampled to 10 minutes.
So far I’ve followed this example, by creating;

Retention Policies -

name     duration  shardGroupDuration replicaN default
----     --------  ------------------ -------- -------
one_hour 1h0m0s    1h0m0s             1        true
one_year 8736h0m0s 168h0m0s           1        false

and a Continuous Query -

CREATE CONTINUOUS QUERY "cq_5m" ON "emoncms" BEGIN
SELECT mean("grid") AS "grid",mean("solar") AS "solar", mean("divert") AS "divert", mean("usage") AS "usage"
INTO "one_year"."downsampled_iot"
FROM "iot" GROUP BY time(5m) END

The problem is that I was expecting (and hoping!!) that as soon as the 5 minute averaging had been done, and the results written to "one_year"."downsampled_iot", that the data used for those calculations would then be deleted from the measurement iot, but instead, there is a considerable delay, and I have overlapping data in both measurements - somewhere between a 1hr & 2hr overlap.

My ultimate goal is to run a query to combine data from both iot & one_year"."downsampled_iot and display it in a single Grafana chart, so that I can see greater precision for the previous hour (for fault inspection).

EDIT - tried setting the one_hour retention policy to 5 minutes, but get ERR: retention policy duration must be at least 1h0m0s.

2

If this is not possible just using a Continuous Query, would I be able to Join both measurements using a criteria?
i.e.
Join one_year"."downsampled_iot" WHERE time > now()-1hr AND "iot" WHERE time <= now()-1hr

So I’d get the previous hour of data at 5 second precision, and anything older that 1 hour would be downsampled data?

I’m using Influx v1.7.2