I was working on a flow and had a typo for where my data was going which resulted in combining 2 temperature sets. I noticed it pretty quickly so I have around 30 minutes of data where I need to clean things up. Fortunately, the values are far apart so I can easily select them in the influx interface. I was hoping someone here might be able to help clear those. If not, I can ask on the influxdb community.
I have an outdoor weather station reporting temperatures in the 30-50°F range since I started the flow/database. I have a temp sensor that is indoors reporting ~68°F. There is a 30 minute period where these are combined. If I use the influx cli I can use a command like this to display the data:
> select * from "rtl_433/Acurite-5n1/A/temperature_F" where value > 60 limit 10
name: rtl_433/Acurite-5n1/A/temperature_F
time value
---- -----
1611345517862054888 69.98
1611345534624218033 69.98
1611345550936809045 70.16
1611345567639995886 69.98
1611345584503504227 69.98
1611345600706825760 69.98
1611345617452314045 69.98
1611345634224615565 69.98
1611345651093932895 69.98
1611345667253789482 69.98
>
If I try to use the delete command as I understand it, it doesn't work:
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where value > 60
ERR: shard 7: fields not supported in WHERE clause during deletion
>
Any help would be appreciated. This is something I can see easily happening so it would be good to know how to fix it.
I found some threads that suggest there is no mechanism to perform this task. I deleted them manually by exporting a list of bad values and taking them to Excel and then generating a list of 1-off delete statements based on time. There has to be a better way. If anyone knows of one, please share!
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where time=1611345567639995886
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where time=1611345584503504227
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where time=1611345600706825760
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where time=1611345617452314045
> delete from "rtl_433/Acurite-5n1/A/temperature_F" where time=1611345634224615565
...
As the error says, you can't use a where clause when Deleting, except on the time column. You will have to use where time >= nnn and time <= nnn. Or where time = nnn.
I often do such things in a node-red flow using the influx In node to run the initial query, then build the delete query and use the influx out to run it, but only connecting up the wire to the Out node after I have carefully checked the query the flow generates. Doing that you can pick up the timestamps from the select and automatically build the delete query.
I didn't understand the error on the delete statement other than it wasn't happy with my where clause. I read that it could only contain time so wasn't sure if there was another variation of command that would work better. It does sound like my work around is similar to what you do, albeit in a much MUCH more elegant way. I'll keep your approach in mind.
