I submit this to assist others who may ned to know how to delete data from InfluxDB via the InfluxDB UI in HASSIO.
Go to explore and query for the following SHOW MEASUREMENTS ON "<db_name> eg: homeassistant" SHOW TAG KEYS ON "<db_name>" FROM "<measurement_name> eg: °C"
Query template: USE "<***db_name***>"; DELETE FROM "<***measurement_name***>" WHERE "<***tag***>" = '***tag_value***' AND time < '2021-04-04'
Actual query eg: USE "homeassistant"; DELETE FROM "°C " WHERE "entity_id" = 'tasmota_analog_temperature_2' AND time < '2021-04-04'
Here is an additional link explaining some concepts: InfluxDB
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.
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:
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.
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"
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
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?
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
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.
