How to delete data from InfluxDB tables

Hi all,

just found out how to delete data from influxdb tables. I learned that you can only use the time key to define a range or a specific date to create a deletion condition and the time value has to be in UNIX timestamp format. There is no way to use the item values to define a deletion query !!!

Here is an example where you start with a list of your influxdb MEASUREMENTS which represent your items:

Run either in UI or cmd line: > SHOW MEASUREMENTS gives you the list of your items.

Choose one item and you can list the entries of an item table with

> SELECT * FROM item_table

In the Admin UI you get an output with time in the “normal” date format:

Running this command on the command line gives you an output with time in the UNIX timestamp format in milliseconds:

> select *  from ZwaveDvTemp
name: ZwaveDvTemp
time                value
----                -----
1546859971820000000 68.84
1546860001613000000 68.84
1546860591453000000 20.46
1546860602007000000 20.46
1546861201789000000 20.46
1546861801919000000 20.46
1546862400006000000 20.46
1546862774547000000 67.65
1546863000004000000 67.65
1546863600004000000 67.65
1546864200004000000 67.65
1546864800009000000 67.65
1546865400004000000 67.65
1546866000004000000 67.65
1546866600003000000 67.65
1546867200004000000 67.65
1546867800002000000 67.65
1546868085845000000 68.84
1546868400004000000 68.84
1546869000002000000 68.84

To get the appropriate value for defining a deletion query you have to:

  • either transform the normal date format from the InfluxDB UI into UNIX timestamp in seconds:
    => normal date: 2019-01-07T00:00:00.000Z
    => UNIX timestamp: 1546815600000
    e.g. use this converter: click_here

  • or you query your intended time stamp/range through the command line from the InfluxDB which is given in milliseconds and convert to seconds by dropping the last six zeros 000000
    => 1546815600000000000
    => 1546815600000

Now you have everything to create your deletion query:

  • for a certain time stamp (example here time = 2019-01-07T00:00:00.00):
> DELETE FROM ZwaveDvTemp WHERE time = 1546815600000

deletes a single value in the table at time: 2019-01-07T00:00:00.00
.
.

  • for a time range (time > 2019-01-07T00:00:00.00 and time < 2019-01-08T00:00:00.00):
> DELETE FROM ZwaveDvTemp WHERE time > 1546815600000 and time < 1546902000000

deletes a range of values in the table between time: from 2019-01-07T00:00:00.00 to 2019-01-08T00:00:00.00
.
.

  • delete everything after a certain time stamp (time > 2019-01-07T00:00:00.00)
> DELETE FROM ZwaveDvTemp WHERE time > 1546815600000

.
.

  • delete everything before a certain time stamp (time < 2019-01-08T00:00:00.00)
> DELETE FROM ZwaveDvTemp WHERE time < 1546902000000

.
.
From this point on you may derive other manipulation queries in influxdb …

Have Fun!
Justus

8 Likes

This is cool! Thanks.

I am still setting up and fooling around with openhab, just to get the hang of it. Deleting ‘test’ records form influx was one of the things on my list I had to sort out. This post saves me a lot of time.

Hi.
Can You help me find an Admin UI for InfluxDB on openhabian. I can’t find this window nor in Paper UI, nor i Grafana, only two places where any information of InfluxDB is configured.

Sorry @Dmitry_Kravchenko,
I can not help you here, as I am using OpenHAB in a docker container environment. My influx db instance runs in a separate container and I reach the UI via web browser on the ip address and the default port. Normally you access the InfluxDB UI via web browser http://<your_openhabian_ip>:<port_number> assuming that your influxdb instance is installed on your openhabian and using the default port number which is normally 8086. In some documentations there is another port 8083 used …
I am confident that you access the UI via the web browser you only have to search for the right port. Maybe there is a config file for the InfluxDB where port to access the UI is specified?

Sorry, but this is just a good guess. Maybe you should ask someone who is running openhabian …

Since version 1.3, there is no admin GUI anymore… see InfluxDB docs. Use the command line instead.

And with command precision rfc3339you get readable dates… ;o)

For remote access i use InfluxDBStudio, but it have no possibility to delete data. some my sensors sent wrong data for a small period and this data get me wrong averages and other calculations. Before a time I used MySQL, and have no problem to delete or update data, but now I see no possibility to clean wrong data at all.
Is it possible to clean periods of data?

Yes, if what you mean is a time range. It’s in the original post that started the thread above.

Normally you do this on the InfluxDB machine locally from the command line. But I think you can do this remotely with curl since the command line is connecting to the localhost port 8086 anyway.

By the way, to solve the problem you have, I implement a raw measurement Item and sanity check it in a rule before passing it on to the real Item that will have persistence. I check for temperatures outside of the normal range and toss them out if they are not in range.

Hi all! I got sick and tired of not being able to “delete where value” so I created a C# app that will delete values not within the range set by the user. See attached TXT file showing how it works. Let me know if you want to try it out…influx_clean.txt (20.6 KB)