Delete specific data

InfluxDB 1
1

I know this is something that’s been asked many times but I haven’t located a specific answer that I have succeeded in utilizing. I apologize in advance.

I have a number of weather related measurements being recorded into an influxdb 1.8 database residing on an old RPi1 (hence I can’t upgrade to Influxdb 2.0 currently). Generally the data is valid, but there are some that get decoded wrong and show up way off the scale of reality.

image
image1361×230 21.8 KB

From what I’ve read it appears that it may be possible to delete data by indicating the date/time field as the key. If that’s the only option, I guess I’d have to filter data for values greater than some value for instance:

> select * from "rtl_433/Acurite-Tower/B/temperature_F" where "value" > 100

name: rtl_433/Acurite-Tower/B/temperature_F
time                           value
----                           -----
2021-07-17T02:08:23.968020844Z 905.719982
2021-08-24T16:12:19.944029967Z 1684.0399819999998
2021-08-24T16:37:33.020477072Z 1684.0399819999998
2021-08-24T18:43:36.837907375Z 1695.560018
2021-08-24T19:28:13.296592154Z 1695.560018
2021-08-28T17:12:25.410487174Z 1695.560018
2021-08-28T18:13:22.800103943Z 1695.560018
2021-08-28T18:18:05.406296533Z 1695.560018
2021-08-28T18:40:48.439984996Z 1695.560018
2021-08-28T18:56:19.134400212Z 1695.560018
2021-08-28T19:06:01.166229011Z 1695.560018
2021-08-28T19:22:55.114183116Z 1695.560018
...

There are between 20 and 50 bad values currently but since they seem to pop up randomly, I was hoping that a simple command or script could find and delete these values without requiring manual intervention. As you can see from the image, I have Chronograf running too in case that would be preferable. If I could be directed to any best practice method, I’ll go with that. Thanks!

2

you can use DELETE with a where that specifies a time range and/or tag values, but not field values. (docs here)

in your case (from what I can see) you will have to use the time range, and there is no way to build a single script that identifies and deletes the data.
Your query to find anomalies is fine, just use those timestamps in a DELETE, as a sample:

DELETE FROM __Measurement__ WHERE time = '2021-07-17T02:08:23.968020844Z'

here you can find more about supported time formats

If you use Chronograf you can run multiple select queries by separating them with ;, I expect it to work even with deletes, from the CLI a newline is all you need (as you cannot have multiline commands)

3

I was able to use this approach to delete the few points that were in the set. I recently found where they came from and corrected it so hopefully no new issues will occur.