Home Assistant SQlite - Statistik-Daten ändern
Ursprünglich hatte ich beim Auslesen des Smartmeters immer wieder mal falsche Werte in der Datenbank. Die Ursache konnte ich zwar korrigieren, die falschen Werte machen sich aber nicht so gut in der Statistik. Erst nachdem ich einige Zeit damit verbracht habe die Statistik-Daten direkt in der Datenbank zu korrigieren, habe ich entdeckt, dass falsche Statistik-Werte einfach in Home-Assistant geändert werden können.
In den Entwicklerwerkzeugen, im Reiter "Statistik" können die einzelnen Statistik-Werte korrigiert werden:
Daten von falschen Einträgen direkt in der Datenbank bearbeiten
Bei der Integration meiner Wasseruhr habe ich die Einheit von m³/h auf Liter geändert und dabei falsche Werte in die Datenbank gespielt. Die SQLite-Datenbank kann über Linux-Board mitteln mit dem Befehl sqlite ausgelesen und bearbeitet werden.
Zunächst verbinde ich mich in den Docker-Container von Home Assistant:
docker exec -it home-assistant /bin/bash
Im Docker-Container kann mit folgendem Befehlen sqlite installiert und die Datenbank verbunden werden:
bash-5.1# apk add sqlite
bash-5.1# sqlite3 /config/home-assistant_v2.db
Die Verbindung auf die Datenbank erlaubt es beliebige Datenbank-Queries auszuführen. Als Beispiel können bestimmte Einträge mit "select" ausgegeben oder mit "delete" gelöscht werden:
sqlite> select * from states where entity_id = "sensor.water_value" and last_updated < "2022-12-22 22:56:48.421279";
sqlite> delete from states where entity_id = "sensor.water_value" and last_updated < "2022-12-22 22:56:48.421279";
sqlite> delete from states where entity_id = "sensor.water_state" and state > "890100";
Siehe auch: Home Assistant SQlite - Statistik-Daten ändern
Daten direkt in der SQLite-Datenbank mit VSCode ändern
Etwas konfortabler als sqlite ist die VSCode-Extension SQLite-Editor:
Informationen zu VSCode, siehe: "die 4 besten Tools um Text-Dateien zu vergleichen" und "PowerShell Editoren im Vergleich: ISE, Visual Studio Code"
Mit der VSCode-Extension SQLite3 Editor, können die Datenbank-Dateien einfach im Editor geöffnet werden:
Per Dropdown können die einzelnen Tabellen der Datenbank ausgewählt und angezeigt werden:
Zudem kann eine eigene SQL-Query für die Abfrage verwendet werden.
Als Beispiel wollte ich die Werte eines bestimmten Sensors in einem bestimmten Zeitraum genauer ansehen:
SELECT * FROM "states" where entity_id = "sensor.meter01_active_energy_plus" and last_updated > "2023-01-14 06:00" and last_updated < "2023-01-14 12:00";
Ausgeführt werden die Queries durch Drücken von Umsch + ENTER:
Einzelne Werte können direkt in der Tabelle geändert werden:
Metadata_id herausfinden
Anzeige aller Statistik-Daten eines Sensors:
SELECT * FROM "statistics_short_term" where metadata_id = "86"
unbedingt Home Assistant stoppen und ein Backup das Datenbankfiles "home-assistant_v2.db" anlegen!
Update eines einzelnen Werts mittels Query
update "statistics_short_term" set state="95197.0" where id=744412;
Update der Spalte "SUM" in Statistics
Achtung: Vor dem Ausführen muss die metadata_id angepasst werden, im Beispiel 86:
Alle SUM-Werte auf NULL:
update "statistics" SET SUM = null where metadata_id = "86";
Den ersten Wert mit 0 befüllen:
update "statistics" SET SUM = 0 where rowid IN (SELECT rowid
FROM statistics
WHERE metadata_id = "86"
LIMIT 1);
Update
WITH t (id) AS (SELECT rowid FROM statistics WHERE SUM IS NULL AND metadata_id = "86") Update "statistics" SET SUM = ( (SELECT SUM FROM "statistics" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1) + (STATE - (SELECT state FROM "statistics" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1)) ) where rowid IN (SELECT id FROM t)
Tabelle statistics_short_term
Alle SUM-Werte auf NULL:
update "statistics_short_term" SET SUM = null where metadata_id = "86";
Der Update in SQLite kann über die rowid erfolgen, was das Update-Statement im Vergleich zu SQL etwas aufwändiger macht.
Der Anfangswert kann dann wie folgt befüllt werden:
update "statistics_short_term" SET SUM = "2058.0" where rowid IN (SELECT rowid
FROM statistics_short_term
WHERE metadata_id = "86"
LIMIT 1);
Folgendes Query befüllt die restlichen SUM-Werte erneut
WITH t (id) AS (SELECT rowid
FROM statistics_short_term
WHERE SUM IS NULL
AND metadata_id = "86")
Update "statistics_short_term" SET SUM = (
(SELECT SUM FROM "statistics_short_term" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1) +
(STATE - (SELECT state FROM "statistics_short_term" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1))
)
where rowid IN (SELECT id FROM t)
Worst Case: Statistikdaten neu aufbauen
Nachdem der Home Assistant Recorder die Details der letzten 10 Tage in die Datenbank schreibt und diese von der History-Integration behandelt werden, können die Statistikdaten der letzten 10 Tage von den Details rekonstruiert werden. Durch das Löschen der History-Tabellen in der Datenbank befüllt Home Assistant diese beim nächsten Start mit den Daten der letzten 10 Tagen neu.
Beim Löschen der Statistiktabellen werden nur die Daten der letzten 10 Tage wiederhergestellt.
Home-Assistant stoppen:
docker-compose down
Dann mittels SQLite die folgenden Tabellen löschen:
Delete FROM "statistics_runs";
Delete FROM "statistics";
Delete FROM "statistics_short_term";
Home-Assistant wieder starten:
docker-compose up -d
Die letzten Statistikdaten werden jetzt im Hintergrund wieder aufgebaut:
Die Tabelle "statistics_runs" zeigt den Fortschritt:
Die Daten enthalten initial nur 2 Einträge pro Tag ..
Fazit
Einzelne Werte können sehr einfach direkt in Home-Assistant geändert werden. Die von Home-Assistant verwendete SQLite-Datenbank hat im Vergleich zu SQL oder MySQL einige Limitationen, dennoch kann die Datenbank direkt aufgerufen und bearbeitet werden. Als Alternative zu SQLite kann in Home-Assistant auch MySQL als Datenbank verwendet werden, siehe: Home Assistant Datenbank MySQL vs. SQLite.

{{percentage}} % positiv
