I use the integration of InfluxDB 1.8 to visualize energy data with Grafana, among other things.
The retention policy of the Homeassistant database is currently unlimited, which I would like to change.
I have created a new retention policy “infinity”.
However, not all data should be moved. Only data of the entity_id`s beginning with “scb*” should be moved to “infinity”.
For that I would have to create a CONTINUOUS QUERY.
Unfortunately I can’t get any further here.
With this command all data of the measurement “kWh” are copied. But that should still be selected to all: entity_id = scb*…
CREATE CONTINUOUS QUERY "scb" ON "homeassistant"
BEGIN
SELECT mean("value") AS "value"
INTO "homeassistant"."infinite"."kWh
FROM "homeassistant"."autogen"."kWh"
GROUP BY time(1h), entity_id
FILL(previous)
END
Here I have selected several data sets of “entity_id = scb*” in Chronograf:
SELECT mean("value") AS "mean_value" FROM "homeassistant"."autogen"."kWh" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND ("entity_id"='scb_battery_charge_from_pv_month' OR "entity_id"='scb_battery_charge_from_pv_total' OR "entity_id"='scb_battery_charge_from_pv_year') GROUP BY time(:interval:) FILL(null)