Genius, thanks a lot. I had my syntax wrong.
Thank you!
Thanks, very helpful
Nice.
Anyone has a query to delete any/all data and entity itself from influx?
Cheers
My approach to cleanup the InfluxDB:
Execute the query: SHOW TAG VALUES ON "homeassistant" WITH KEY = "entity_id" and create a dashboard of if.
You get an overview of entities in the DB.
Next, you can delete unwanted entities completely:
Exclude the entity from being written to the InfluxDB again.
EDIT: I personally prefer the include-only configuration of the InfluxDB. This prevents the test entities to be written to the long-term InfluxDB. If a HA entity becomes stable and it has sense to collect its data, I include this entity to the DB configuration.
9 Likes
hey is it ok to show me step by step , i tried to copy your query but it did not execute the command and gave me an error message. thank you
I’ve just found this thread and was happy, because it could solve an issue I’m currently facing, but I stumbled upon something. When I execute the command, I’m getting the following result:
But after that, the entity is gone. I figured this out by accident, because I wanted to document my steps.
So the idea is working and values can be deleted, but the UX is not really good.
That is to be expected. Your query only deletes measurements, it does not return any.
Somehow I disagree. The leading sign indicates a warning, but there is nothing to be worried about. Further the sentence says something like: ‘Basically you did a good job, BUT …’
Formally this is correct, but simply misleading.
Exactly, “warning” not “error”. Just something for you to check and make sure that is what you wanted.
For my own future reference and anyone looking to clean up data spikes over a specific time range, here’s what worked for me:
USE "<db_name>"; DELETE FROM "<measurement>" WHERE ("<tag>" = "<tag_value>") AND time > "yyyy-mm-dd hh:mm:ss" AND time < "yyyy-mm-dd hh:mm:ss"
Example if deleting all data on a given day:
USE "home_assistant"; DELETE FROM "kWh" WHERE ("entity_id" = "gas_daily") AND time > "2022-05-30 00:00:00" AND time < "2022-05-30 23:59:59"
4 Likes
Thank you for this detailed info about how to delete entries from InfluxDB! For me, InfluxDB is far from intuitive, but I had to do something because my database size was 50 gigabytes already, although I had only included sensor domain.
My first step was to select only those entities I knew I wanted long-term data OR I suspected I might need long-term data. That was 90-100 sensors total, so I replaced my include sensor domain with huge list of single entities that I included to InfluxDB. I checked for a couple of days and seems like my database didn’t grow as fast anymore.
Then it was a long day of copy-pasting sensor names and measurement types and removing them one by one as guided by @belkop-ghb . Now, instead of 50GB, my InfluxDB is 5 GB. 
It’s clear that Home Assistant need some sort of easy way to managing short and long term history, and maybe one day we have easier way to do that compared to what we have now, but at least now I don’t have immediate concerns about my SSD drive getting full because of InfluxDB
1 Like
Hi all, I’ve tried to follow the previous instructions multiple times, which is to delete some data within a certain time scale. All I get is errors or a ‘chronograf’ and my data still persists.
Any suggestions please
USE "home_assistant"; DELETE FROM "°C" WHERE ("entity_id" = "bme280_temperature_2") AND time > "2023-04-01 23:15" AND time < "2023-04-06 18:10"
I have a spike in my measurement, which happened for the first time ever. So I found this thread, however, the following syntax, which is based on the influxdb metaquery template, does not delete anything:
USE "homeassistant"; DELETE FROM "kWh" WHERE "entity_id" = 'all_lights_calc_energy' AND time > '2023-04-13 14:15:00' AND time < '2023-04-13 14:27:00'
Any ideas on how to delete spikes in energy measurements?
1 Like
The problem was in
WHERE "entity_id" = 'all_lights_calc_energy'
I have not solve it, but simply deleted all kWh… fine, the command finally worked and I could restore my HA from two days ago… next time, I will create a sandbox for influxdb
Hello,
can someone help me to remove a DataBase and all it’s data.
If i remove a DataBase, there is no extra space, so it don’t delete the data.
I want to delete all the initial measurements from my zigbee devices before i gave them sensible names.
For example ‘0xa4c1385294b36ffd’, ‘0xa4c1385294b36ffd_battery_low’, ‘0xa4c1385294b36ffd_occupancy’ etc.
Instead of going though and deleting each measurement manually I’ve tried using wildcards, but I get errors.
USE "HomeAssistant"; DELETE FROM "state" WHERE "entity_id" LIKE '0xa4c1385294b36ffd%'
returns
error parsing query; found LIKE, expected ; at line 1, char 39
then
USE "HomeAssistant"; DELETE FROM "state"; WHERE "entity_id" LIKE '0xa4c1385294b36ffd%'
returns
error parsing query: found WHERE, expected SELECT, DELETE, [...] at line 1, char 22
Can anyone help me with my syntax?
Ideally i’d use something like
USE "HomeAssistant"; DELETE FROM "state"; WHERE "entity_id" LIKE '0x%'
since no other entities start with ‘0x’
Edit:
I figured it out
USE "HomeAssistant"; DELETE FROM "state" WHERE "entity_id" =~ /^0x/
I’m looking at doing some deleting but my InfluxDB dashboard doesn’t seem to recognise DELETE as a ‘key #’ word (ie it doesn’t colour it in).
The auto generated SQL line for selecting looks like
SELECT mean("value") AS "mean_value" FROM "homeassistant"."autogen"."°C" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "entity_id"='boiler_cabinet_temperature' GROUP BY time(:interval:) FILL(null)
and works fine, which I edited to
DELETE FROM "homeassistant"."autogen"."°C" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "entity_id"='boiler_cabinet_temperature'
I get the error message
error parsing query: retention policy not supported at line 1, char 1
Any ideas?
Edit:
I figured it out (Doh!)
USE "homeassistant"; delete FROM "%" WHERE "entity_id" = "moisture_dracaena_battery"
This could take a long time deleting all the data I never wanted in the first place (but when I started I knew nothing). And I still don’t know how to purge the database file to reduce its size.
I struggle a lot with deleting a single wrong reading from my InfluxDB. Can anyone help me out with all correct references (i am a noob in queries)?
Query format needed:
USE “energy”; DELETE FROM “Solar” WHERE (“Calculated” = “sajoostvermogen”) AND time > “2024-04-11 14:24:00” AND time < “2024-04-11 14:28:00”
Database format:
How do I get to the right location with this query?