Solved : Noob question over data management in InfuxDB

Telegraf
,
1

Hello,

I’ve been using Influxdb 0.9 together with Domoticz and Grafana for 1 month. I get nice charts, everything would be perfect if I could manage a little bit the data stored in influxdb. Just a basic example : due to a bug in my software, some of my temp have been reporting random temps between 30°C and 100°C during some hours. Well, I’ve spent hours trying to find how to delete them.

This don’t work :

delete FROM blah WHERE time < ‘2018-02-02’ and time > ‘2018-02-01’
–> error parsing query: retention policy not supported at line 1, char 1

This don’t work :

drop series from blah where time < ‘2018-02-02’ and time > ‘2018-02-01’
–> error parsing query: retention policy not supported at line 1, char 1

The only way to ‘delete’ I found is to export the database in a file, find the timestamp of the serie to delete, then write a new serie using the same timestamp. I won’t do this for the 300 temps that need to be effectively deleted.

It is worst with tags, I have many test tags I used at the beginning on real data, it looks like it is not possible to update them to the right value.

I hope I missed something, my system is not perfect, I must be able to clean data easily.

Thank you by advance for your help.

grwe7gHzRpiomVtI3AeXtw.png542×504 38.4 KB

2

You can delete it using the either of the following timestamps

  1. Time Format RFC3339: ‘2018-02-13T00:02:05Z’
  2. Epoch (with s at the end): 1518499082s

sample

delete from cpu where time > ‘2018-02-08T00:00:00Z’ and time < '2018-02-08T02:00:00Z’
delete from cpu where time > 1518422400s and time < 1518499082s

3

Thank you for your help.

I get the following error :

error parsing query: retention policy not supported at line 1, char 1

If I use this formula :

drop series from “domoticz”.“autogen”.“device_exterieur_temp” where time > “2018-02-07T05:00:00Z” and time < “2018-02-08T16:17:00Z”

I get no error, but nothing happens, and data is still here.

If I use this :

select * from “domoticz”.“autogen”.“device_salon_temp” where time > ‘2018-02-08T14:00:00Z’ and time < ‘2018-02-08T15:00:00Z’

I get :

invalid operation: time and *influxql.VarRef are not compatible
I’ve tried numerous combinations of syntax (no quotes, simple quotes, double, etc.), none worked. I googled before, but it doesn’t work for me :

4

Can you try the following

delete from “domoticz.autogen.device_salon_temp” where time > ‘2018-02-08T14:00:00Z’ and time < ‘2018-02-08T15:00:00Z’

Drop should be use when you are deleting the entire measurement or when “where” clause is not used.

5

I already tried before, I tried again, fail.
If I launch your query, I get a syntax error because of the quote. If I correct the quotes, I get a “retention policy not supported” error.

6

I assume this is the measurement name “device_salon_temp” and the database name is “domoticz”. If you are under this database and try the delete command does it work?

delete from device_salon_temp where time > ‘2018-02-08T14:00:00Z’ and time < ‘2018-02-08T15:00:00Z’

7

Your query returns a syntax exception.
I based my query on the following working query taken from the chronograf data explorer :

SELECT mean(“value”) AS “mean_value” FROM “domoticz”.“autogen”.“device_SALON_TEMP” WHERE time > now() - 1h GROUP BY :interval: FILL(null)

8

The syntax error seems to be related to the name mismatch

Incorrect:

delete from device_salon_temp where time > ‘2018-02-08T14:00:00Z’ and time < ‘2018-02-08T15:00:00Z’

Correct should be:

delete from device_SALON_TEMP where time > ‘2018-02-08T14:00:00Z’ and time < ‘2018-02-08T15:00:00Z’

You can always check through the command line by using the following command:

show measurements

9

This query worked. The conclusion is that it can only be triggered from console. Thank you for your patience, really.

1 Like