CONTINUOUS QUERY in InfluxDB

Configuration
1

I want to create a continuous query in Influxd but can’t figure out how to do it. Can I do it from the InfluxDB UI or do I have to do it from a CLI. If that is the case how do I access the CLI?

I’m running hassio on a Pi with Influx installed as an add-on.

1 Like
2

you found the solution for this?
I’m in the same situation… where the damm influxdb CLI?

3

Hi

Sorry for late respons.

I did figure it out:

This is the query I created:

CREATE CONTINUOUS QUERY chh ON home_assistant BEGIN SELECT 21 * exp(-1 * mean(value) * 0.150) + 83 AS value INTO home_assistant."60d".high FROM home_assistant.autogen."°C" GROUP BY time(1h) END

I created a continuous query called chh that performs a calculation on mean(value) and stores it in home_assistant.60d retention policy

Value is a temperature in home_assistant.autogen, hence “°C”

4

I’m trying to send command in Explore pane with result: Your query is syntactically correct but returned no results.
And continuous query not working.

5

Hi

I’m not sure I understand your question, What’s the query you are trying to run?

6

I have a couple DBs in influx to separate out data from a couple sources. I’m able to run GRANT queries using the explore pane and I get that same message, but it does apply the permissions I set, so I’m surprised a continuous query wouldn’t behave the same…
which also: darn, I was hoping that would work! :stuck_out_tongue:

7

So if I was trying to run the continuous query that you are running, where do I type it in? Is there a spot in the InfluxDB addon somewhere, or did you find a way to get to the CLI?

8

I ran the continuous query command on the Query tab in the Explore pane.

A couple of things to check:

  • What happens if you run “show continuous queries”, is the continuous query added? Sometimes I have to close all tabs, run the command and switch to Table Graph to see results
  • Is there any data if you click “value” under “high” on the retention policy “60d”, or whatever names you used?
1 Like
9

Unfortunately show continuous queries doesn’t show anything

10

Can you share the query you are using?
What is the target retention policy called?
What is the name of the retention policy, Tag and field you are fetching data from?

11

I have been made an retention policy longtime as infinity.

CREATE CONTINUOUS QUERY "cq_tempC_1m" ON "homeassistant" BEGIN
SELECT entity_id, value
INTO "homeassistant"."longtime"."°C"
FROM "homeassistant"."autogen"."°C"
GROUP BY time(1m)
END

This didn’t fill any data into database too.

12

Hi

I’m by no means an expert but try to change the SELECT to:
SELECT entity_id, mean(value)

I think that gives you the average over the time period

13

I’ve been change SELECT part to SELECT entity_id, mean(value) AS value, but retention policy is not filled with new values.
I find combo box Metaquery Template and SHOW CONTINUOUS QUERIES in it and it works. So I have saved one CQ now, but it don’t add any data to RP.

14

Did you eventually get this resolved? Facing the same problem over here…

16

No, not found solution.

17

Could the problem be that you are selecting both “entity_id” and “mean(value)” and then trying to store that as “value”. Remove entity_id?

18

Can you post your query? @markdebontacc

19

I tried the instructions from : Influxdb (1.8) setup - ONE continuous query for WHOLE database but didn’t got very far.

CREATE DATABASE “home_assistant” WITH DURATION 4380h0m0s REPLICATION 1 SHARD DURATION 168h NAME “6M_raw”
influxdb retention policy conflicts with an existing policy

So I created this from InfluxDB (add-on)

then this step:

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

Request failed with status code 400

then gave up ;-(

20

Also tried the steps as mentioned below:

https://alex3305.github.io/home-assistant-docs/add-ons/influxdb-downsampling/

But no data is stored with the continuous query ;-(

21

I was able to get the following to work:

CREATE CONTINUOUS QUERY "autogen-1mth_30min" ON homeassistant BEGIN SELECT mean(value) AS value INTO homeassistant."1mth_30min".:MEASUREMENT FROM homeassistant.autogen./.*/ GROUP BY time(30m), * END

You have to wait till the next cycle (every 30 minutes or so) for this to run in order to start seeing data in the new location (1mth_30min for me). To get all your previous data moved over, I ran:

SELECT MEAN(value) AS value INTO homeassistant."1mth_30min".:MEASUREMENT FROM homeassistant.autogen./.*/ WHERE time >= '2020-01-01T00:00:00Z' AND time <='2022-06-18T00:00:00Z' GROUP BY time(30m), *

This took all the old data and aggregated it to the new table.

In the HASS dashboard, I have influxdb installed. I click on the icon for it in the left shortcuts menu, then go to explore, then fill this into the query box and select “submit query”. You can also see your existing continuous queries with “SHOW CONTINUOUS QUERIES”.