Deleting invalid records from InfluxDB?

[ibmaster]

Hi,
I am no database expert, but there are some invalid entries in my powerwall InfluxDB for some reason or another.
Anyone have recommendations (i.e. specific commands / queries) for how to routinely cleanup the powerwall database from these bogus records?

[ibmaster]

I edited the tesla-history.py code to be able to delete any time range in the Influx DB (not conditioned just by the Tesla cloud data) using the --remove CLI option. And now all the garbage data entries have been cleaned out. Thanks! :)

[mcbirse]

It's very strange that you appear to have had data points logged with a value of zero. Normally (and I have tested this) if you had a network outage to the gateway for instance, it simply would not log anything to InfluxDB so there would be a data gap.

To answer this question though, here is the manual way of running any query (like DELETE) on InfluxDB:

# Connect to InfluxDB shell CLI tool
docker exec -it influxdb influx -database 'powerwall'

Connected to http://localhost:8086 version 1.8.10
InfluxDB shell version: 1.8.10
> 

Then at the prompt, you can enter any query you like. e.g. to DELETE data for a time range:

DELETE FROM http WHERE time >= '2022-10-14T17:55:00+11:00' AND time <= '2022-14-14T18:10:00+11:00'

Make sure to enter you own timezone offset (or use UTC format).

You do need to be careful with deleting data however, and understand deleting with InfluxDB will delete from all retention policies.

"DELETE FROM http" actually deletes data from 'http' of all retention policies, e.g.:
autogen.http
grid.http
raw.http
vitals.http
pwtemps.http
etc.

InfluxDB does not support deleting data from a specific retention policy.

i.e. The data you have shown is probably from autogen.http only. If your system was still logging valid data to the other retention policies, then a "DELETE FROM http" is going to wipe that for the time range you enter, even though you may have only wanted to delete from autogen.

[ibmaster]

Thank you @mcbirse for the detailed instructions with Influx DB!
Yes, unclear how those entries are showing up, but my Tesla Gateway/Powerwalls are on WiFi and not hardwired. Maybe it occurred during a firmware update to the system?

[mcbirse]

Interesting - it got me wondering about my system and if it had any zero value data points.

You could do the following to see how prevalent this is?

Run the influx CLI tool (you can add -precision 'rfc3339' option to output times in a readable format).

# Connect to InfluxDB shell CLI tool
docker exec -it influxdb influx -database 'powerwall' -precision 'rfc3339'

Then query for anything where all values in autogen (from_grid, from_pw, etc.) are equal to zero.

Connected to http://localhost:8086 version 1.8.10
InfluxDB shell version: 1.8.10
> SELECT * FROM autogen.http WHERE from_grid=0 AND from_pw=0 AND to_grid=0 AND to_pw=0 AND home=0 AND solar=0 AND percentage=0 tz('Australia/Sydney')
name: http
time                      from_grid from_pw home host month percentage solar source to_grid to_pw url year
----                      --------- ------- ---- ---- ----- ---------- ----- ------ ------- ----- --- ----
2022-06-29T23:56:00+10:00 0         0       0         Jun   0          0            0       0         2022
2022-06-29T23:57:00+10:00 0         0       0         Jun   0          0            0       0         2022
2022-06-29T23:58:00+10:00 0         0       0         Jun   0          0            0       0         2022
2022-06-29T23:59:00+10:00 0         0       0         Jun   0          0            0       0         2022
2022-06-30T00:00:00+10:00 0         0       0         Jun   0          0            0       0         2022
>

Mine resulted in a few data points like that. I expect we should never have all zero's normally.

It's quite possible perhaps it happened during a firmware update? I have noticed previously during firmware updates, the soe value was returning 0 when my battery charge was definitely not 0!