CONTINUOUS QUERY in InfluxDB

[lcf (Luis)]

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

[RasmusOlsson]

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”

[vpa (Vaclav Panenka)]

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.

[RasmusOlsson]

Hi

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

[FreelancerJ]

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!

[FreelancerJ]

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?

[RasmusOlsson]

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?

[vpa (Vaclav Panenka)]

Unfortunately show continuous queries doesn’t show anything

[RasmusOlsson]

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?

[vpa (Vaclav Panenka)]

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.

[RasmusOlsson]

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

[vpa (Vaclav Panenka)]

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.

[markdebontacc (Mark)]

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

[vpa (Vaclav Panenka)]

No, not found solution.

[RasmusOlsson]

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?

[RasmusOlsson]

Can you post your query? @markdebontacc

[markdebontacc (Mark)]

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

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 a…

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 ;-(

[markdebontacc (Mark)]

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 ;-(

[rwilso03 (Russ)]

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”.

[jove01]

Hello
I can’t create your Query in Explore with my names. The system didn’t accept

:MEASUREMENT

error parsing query: found “MEASUREMENT”

What is wrong, or in which way did you create the Query?

(In another forum I read, that the Web-Admin-Site localhost.8086 didn’t exist no longer!)