How to delete data from InfluxDB tables

influxdb
grafana
delete
Tags: #<Tag:0x00007f0156c8c1f8> #<Tag:0x00007f0156c8c0b8> #<Tag:0x00007f0140123ea8>

(Justus Reich) #1

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


(Remco) #2

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.