Influxdb (1.8) setup - ONE continuous query for WHOLE database

Hey folks

since i haven’t found anything in the home assistant community about this / hardly found anything anywhere else and the help in this forum mainly consists of guesses anyway, here are the settings for a proper influxdb database setup to bring up the quality and lower database size.

All credits go to Janina Trischler from

she found the proper settings for influxdb to create a database, create a retention policy (so your raw data wont be stored forever and gets compressed properly at the right time) and, the most tricky for me, the right CONTINUOUS QUERY to not create one for every measurement (C°, %, V, …). I wrote it down for a new influxdb setup. She also has a part for already existing databases (to not loose existing data when messing with the auto-generated retention policy for home_assistant) in her post.

Just my settings, definitely not a guideline and should be changed for own needs:
Raw Data from home assistant stored 6 months(4380h0m0s). After that it gets compressed / shrunk / downsized to 5 minutes, stored for 2 years (17520h0m0s). After that, 15 minutes for 5 years (43800h0m0s).

Create Database:

CREATE DATABASE "home_assistant" WITH DURATION 4380h0m0s REPLICATION 1 SHARD DURATION 168h NAME "6M_raw"

Create Retention Policy:

CREATE RETENTION POLICY "2y_5m" ON "home_assistant" DURATION 17520h0m0s REPLICATION 1
CREATE RETENTION POLICY "5y_15m" ON "home_assistant" DURATION 43800h0m0s REPLICATION 1

Check:

SHOW RETENTION POLICIES

Create Continuous Querys for the last two retention policys:

CREATE CONTINUOUS QUERY "6M_raw-2y_5m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."2y_5m".:MEASUREMENT FROM "home_assistant"."6m_raw"./.*/ WHERE time < now() -4380h0m0s GROUP BY time(5m),* END
CREATE CONTINUOUS QUERY "2y_5m-5y_15m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."5y_15m".:MEASUREMENT FROM "home_assistant"."2y_5m"./.*/ WHERE time < now() -17520h0m0s GROUP BY time(15m),* END

Check:

SHOW CONTINUOUS QUERIES

for completeness, my influxdb entry in the configuration.yaml

influxdb:
  tags_attributes:
        friendly_name
  exclude:
    domains:
      - group
      - history_graph
      - media_player
      - scan_store
      - zone
      - zwave
      - automation
      - camera
      - frl_store
      - light_store
      - script
      - timer
      - updater
    entities:
      - sensor.date
      - sensor.time
3 Likes

Hi, I had already data in, how can I repack the already existing data?

Thank you very much

Seems that this does the trick, I forgot the * at the end in the beginning…

Good read here: Data Exploration | InfluxData Documentation Archive

SELECT mean(value) AS value INTO "homeassistant"."2y_5m".:MEASUREMENT FROM "homeassistant"."autogen"./.*/ WHERE time < now()GROUP BY time(5m),*

SELECT mean(value) AS value INTO "homeassistant"."5y_15m".:MEASUREMENT FROM "homeassistant"."2y_5m"./.*/ WHERE time < now() -17520h0m0s GROUP BY time(15m),*

Sorry to revive this old thread. I followed the instructions from this and the linked thread and have a question understanding it:

I thought the continuous queries would do the following:
take all data from “autogen” (i have already a database with data in it) which is older than 6 months (for example) and put this in the retention policy 2y_5m with aggregation. So in my understanding, when in look in my influxdb (or with grafana) i would see my entities with stats from before 6 months to 2 years. Since the cq is running continuous i would always have data up to 6 months ago.
But for me, the cq is filling the retention policy with actual data and an actual timestamp (from now), not with the data from past 6 months.
Here are my cq’s:

2y_5m-inf_15min CREATE CONTINUOUS QUERY "2y_5m-inf_15min" ON homeassistant BEGIN SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn INTO homeassistant.inf_15m.:MEASUREMENT FROM homeassistant."2y_5m"./.*/ WHERE time < now() - 730d GROUP BY time(15m), * END
autogen-2y_5m   CREATE CONTINUOUS QUERY "autogen-2y_5m" ON homeassistant BEGIN SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn INTO homeassistant."2y_5m".:MEASUREMENT FROM homeassistant.autogen./.*/ WHERE time < now() - 26w GROUP BY time(5m), * END

And for me it’s filling not data around 6 Months ago but data from now:


(screenshot from the 2y_5m policy).
Can anyone point me a hint what i’m doing wrong?

I just investigated on my own, it seems the continuous queries don’t accept the where clause and ignores it. https://docs.influxdata.com/influxdb/v1.8/query_language/continuous_queries/#the-cq_query

Regarding this the above constellation couldn’t be possible to hold the raw data in the first database until 6 months, copy all older to the 2y_5m database and so on doing by a contiuous query. Can anyone confirm this?