InfluxDB: Removing or deleting data

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


Genius, thanks a lot. I had my syntax wrong.

Very nice solution. Thanks for sharing @ionu

Thank you!

Thanks, very helpful

Anyone has a query to delete any/all data and entity itself from influx?


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.


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"


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. :slight_smile:

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 :slight_smile:

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


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%'


error parsing query; found LIKE, expected ; at line 1, char 39


USE "HomeAssistant"; DELETE FROM "state"; WHERE "entity_id" LIKE '0xa4c1385294b36ffd%'


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’

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?

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.