Home Assistant Datenbank bearbeiten, Statistik korrigieren
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:
Weitere Informationen zu den Statistik-Daten, siehe auch: HA Verlauf: mehr als 10 Tage? Long Time Statistic (LTS)
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. Für den direkten Datenbankzugriff gibt es mehrere Möglichkeiten, als Beispiel SQLite Web oder SQLite3 in VSCode, siehe: ha-sqlite#vscode.
Metadata_id herausfinden
Für die Daten der Tabelle "states" besitzt jede Entität eine metadata_id, diese ist in der Tabelle states_meta hinterlegt. Die Statistics-Tables verwenden eine eigene metadata_id, diese kann wie folgt herausgefunden werden:
Der Wert "statistic_id" ist dabei der Name des Sensors in der Tabelle "states_meta", die id der Wert metadata_id in den statistics-Tables.
Anzeige aller Statistik-Daten eines Sensors anhand der metadata_id:
SELECT * FROM "statistics_short_term" where metadata_id = "86"
Die id kann auch mit folgender Query ermittelt werden:
SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus'
Um für die Anzeige der Daten anstelle der Metadata_id den Namen der Entität zu verwenden, können die beiden Abfragen kombiniert werden:
SELECT * FROM "statistics_short_term" where metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus')
und damit created_ts und start_ts zusätzlich als Datum und Uhrzeit ausgegeben werden, kann die Query entsprechend erweitert werden:
SELECT datetime(start_ts, 'unixepoch', 'localtime') as start_ts_readable, datetime(created_ts, 'unixepoch', 'localtime') as created_ts_readable, * FROM "statistics" 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;
Hinzufügen von fehlenden Werten
INSERT INTO statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) VALUES(NULL,NULL,NULL,NULL,NULL,NULL,1027.4,974.44,228,1704970800,1704970800,NULL);
Für das Zusammenstellen von created_ts und start_ts habe ich einen Konverter online gestellt: Datum umwandeln: Unix Timestamp.
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 = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus');
Den ersten Wert mit 0 befüllen:
update "statistics" SET SUM = 0 where rowid IN (SELECT rowid
FROM statistics
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus')
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
Alte Daten von statistics_short_term wird ohnehin regelmäßig gelöscht, daher ist es meist nicht notwendig diese Tabelle anzupassen, siehe: HA Verlauf: mehr als 10 Tage? Long Time Statistic (LTS).
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 ..
Statistik-Daten von einer anderen DB übernehmen
Zudem ist es möglich Daten einer anderen Datenbank, zum Beispiel von einem Backup in die aktuelle Datenbank zu migrieren. Für bestehende Tables kann wie folgt ein Dump erstellt werden: sqlite
user@server:/var/web$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /var/tmp/home-assistant_v2.db
sqlite> .output /var/tmp/export.sql
sqlite> .dump statistics
sqlite> .dump statistics_short_term
sqlite> .quit
Damit der Dump in eine andere Datenbank eingespielt werden kann, habe ich diesen mit einem Editor bearbeitetet. Hauptsächlich um die id aus dem Dump zu entfernen und die Spalten für den Insert hinzuzufügen:
Regex-Search:
statistics VALUES\([0-9]{1,12},
Replace:
statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) VALUES(
Im Anschluss können die Einträge des Dumps in eine bestehende Datenbank importiert werden:
sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /var/tmp/home-assistant_v2.db
sqlite> .read /var/tmp/export.sql
Statistik von einer Entität auf eine andere übertragen
Nachdem ich den Wechselrichter meines Balkonkraftwerks getauscht hatte, wollte ich die Statistikdaten der Anlage nicht verlieren und diese mit dem neuen Wechselrichter weiterführen. Für das Mapping der Statistik-Daten wird rein die ID des Sensors verwendet:
Wird statistic_id, hier "sensor.balkonkw_total_production" mit einem anderen Sensornamen getauscht: im folgenden Beispiel sensor.balkon_yieldtotal, können die Statistikdaten übertragen werden:
Durch das Umbenennen der statistic_id können sämtliche Werte eines bestehenden Sensors auf einen anderen übertragen werden:
Als zweites Bespiel hatte die ursprüngliche Firmware-Version des Fronius Gen24-Wechselrichters ein Problem mit dem Zähler mit den Ladedaten für meinen BYD-Speicher.
Alternativ können die Werte eines Sensors auch zuvor gelöscht und auf Basis eines anderen Sensors neu erstellt werden
In folgendem Beispiel habe ich die Statistikdaten der Entität: sensor.fronius_mppt_module_2_lifetime_energy gelöscht und mit den Statistikdaten des Sensors sensor.byd_total_charging befüllt.
Tabelle: statistics_meta:
Achtung: Werte in kWh ...
Achtung: Werte in Wh
Statistik für einen bestimmten Sensor löschen:
DELETE FROM statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');
Daten von einem bestehenden Sensor auf einen anderen duplizieren:
WITH target_sensor_statistics_meta_id AS (
SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy'
), source_sensor_statistics_meta_id AS (
SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.byd_total_charging'
)
INSERT INTO statistics (metadata_id, state, sum, created, start, mean, min, max, last_reset, created_ts, start_ts)
SELECT (SELECT id FROM target_sensor_statistics_meta_id), state * 1000, sum * 1000, created, start, mean, min, max, last_reset, created_ts, start_ts
FROM statistics
WHERE metadata_id = (SELECT id FROM source_sensor_statistics_meta_id);
Der Sensor sensor.byd_total_charging ist in kWh, sensor.fronius_mppt_module_2_lifetime_energy in Wh. Die Query macht mit "state * 1000" und "sum *1000" gleichzeitig eine Umwandlung von kWh auf Wh. Ist keine Umwandlung nötig, einfach *1000 für state und sum entfernen ..
Kontrolle:
Die Werte können mit folgender Query kontrolliert werden:
SELECT * FROM statistics where metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');
Werte skalieren
Da ich die gelieferten Daten meines Ultraschalldurchflussmessers erst einige Zeit nach der Inbetriebnahme nachgemessen habe und dabei eine erhebliche Abweichung zu den tatsächlichen Werten festgestellt habe, habe ich die Statistikdaten entsprechend korrigiert:
Alle Statistik-Daten eines Sensors vom Typ "state_class: measurement" um einen bestimmten Faktor: hier 1,33 ändern:
sqlite> .headers ON
UPDATE statistics SET mean = mean*1.33, min = min*1.33, max = max*1.33 WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.flowmeter');
update states SET state = state * 1.33 where metadata_id = ( SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.flowmeter_sum');
Bestimmte Werte korrigieren:
update statistics set min = 6.7 where metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.heating_ruecklauf') AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) > strftime('%Y%m%d', datetime('now','localtime','-3 days')) and min < 5;
Daten von Sensoren des Typs "state_class: total" können wie folgt skaliert werden:
UPDATE statistics SET sum = sum*1.33, state = state*1.33 WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.flowmeter_sum');
update states SET state = state * 1.33 where metadata_id = ( SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.flowmeter_sum');
bestimmte Werte korrigieren:
update statistics SET sum = sum*1.33, state = state*1.33 where metadata_id = (select id from statistics_meta where statistic_id = 'sensor.flowmeter_sum') and sum < 16000 and created_ts > (SELECT unixepoch(datetime('now'),'-5 days'))
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

DANKE für deine Bewertung!
Fragen / Kommentare
(sortiert nach Bewertung / Datum) [alle Kommentare(am besten bewertete zuerst)]
Hallo, auch von mir einen Danke. Ich konnte die entsprechenden Ausreiser auch anpassen. Jedoch habe ich das Problem, dass in der Übersicht bei den kwh immer noch ein falscher Wert steht. Ich findet den angezeigten Wert weder in der states Tabelle noch in den entsprechenden statistics bzw statistics_short_term Tabelle. VG Stephan
Hallo Stephan, hast du die Spalte "sum" in z.B. der Tabelle statistics kontrolliert? Die Ausreißer sind im Wesentlichen der Unterschied in "sum" von einem Wert (einer Stunde) zum anderen (nächste Stunde).
Beitrag erstellt von Bernhard
Hallo Bernhard, Danke für die rasche Antwort. Aber auch Sum habe ich richtig gestellt. Kann ich dir einen Screenshot senden? Der erklärt die Problematik am Besten. LG Stephan
Beitrag erstellt von Stephan
ja, mach mal: kontakt@libe.net
Beitrag erstellt von Bernhard
Hallo, danke für das tolle Tutorial. Mir bleibt folgende Anfängerfrage. Was passiert mit den neu in HA-SQLite-DB eingetragenen oder geänderten Werten in Bezug auf influxdb. Werten die dort auch automatisch eingetragen/geändert? Viele Grüße Thomas
Hallo Thomas, die Sensordaten werden parallel in die SQlite und Influxdb geschrieben. Eine Datenänderung in SQLite ändert keine Daten in der InfluxDB.
Beitrag erstellt von Bernhard
Hallo, ich habe nach einer Anleitung gesucht, mit der ich falsch aufgezeichnete Werte nachträglich korrigieren kann. Es scheint offensichtlich nur über die Änderung der Datenbank zu gehen. Leider bekomme ich mit dem externe VSCode Sql Editor die Meldung, dass die Datenbank gesperrt ist. Wie kann ich die Datenbank für den externen Editor freigeben? Mir ist auch leider nicht ganz klar welcher Wert eines Sensor abgefragt wird, wenn ich ihn über eine Tile Card anzeige. Ist ein Wert aus der Statistic oder state Tabelle. Wenn ich dann den Sensor anklicke wird mir ja ein Verlauf angezeigt. Welche Werte sind das dann, d.h. aus welcher Tabelle werfen sie abgefragt? Über eine Antwort würde ich mich freuen! :-) Gruß Detlef
Interessanter Artikel! Schade nur, dass gerade Anfänger (wie ich selbst einer bin) immer wieder über unvollständige Angaben stolpern. So wird zwar die VSC-Erweiterung "SQLite3 Editor" genannt und die Vorzüge ausführlich geschildert, aber leider keinerlei Hinweise gegeben, wo es denn zu finden und wie zu installieren ist. Mir ist es leider weder unter Add-ons noch im HACS gelungen, das Tool zu finden. Profis werden jetzt sicher die Augen verdrehen und sagen, ist doch völlig offensichtlich - aber diese Leute benötigen bestimmt auch diesen Artikel nicht :-)
Danke für den Hinweis: VSCode ist ein Texteditor außerhalb von Home Assistant. Ich verbinde mich mit VSCode auf den Host auf dem HA als Docker-Container läuft. Die Datenbankdatei könnte aber auch über das Netzwerk kopiert, bearbeitet und wieder hochgeladen werden.
Beitrag erstellt von Bernhard
Danke für den Hinweis: VSCode ist ein Texteditor außerhalb von Home Assistant. Ich verbinde mich mit VSCode auf den Host auf dem HA als Docker-Container läuft. Die Datenbankdatei könnte aber auch über das Netzwerk kopiert, bearbeitet und wieder hochgeladen werden.
Beitrag erstellt von Bernhard
Hallo Bernhard, danke für deine Antwort. VSCode habe ich als Plug-in in HA installiert. Als yaml editor ist der wohl besser als alles andere. Offenbar kann man aber nicht den erwähnten SQLite3 Editor als Erweiterung installieren, dann werd ich es mal von außerhalb versuchen. Mit dem ebenfalls als Plug-in installierbaren SQLite Web komme ich leider nicht klar, ich finde keine Anleitung dazu und noch weniger selbst heraus, wie ich Statistikwerte anzeigen und ändern könnte. Im Github steht nur "This enables you to easily explore all tables and content that is saved in your database." Was ich als guten Lacher empfinde.. :-) Und zu guter Letzt der Reiter Statistik in den Entwicklerwerkzeugen, da sehe ich leider das Rampen-Icon zum bearbeiten nur bei sehr wenigen Sensoren, und leider nicht bei dem wo ich Änderungen vornehmen möchte. Also ganz schön mühsam und holprig, in diese Geschichte einzusteigen. Man braucht schon eine hohe Frustrationstoleranz, da kaum eine der schönen Anleitungen (und ich bin froh, dass sich Leute die Mühe machen!) so funktioniert wie geschildert, da meist die (Anfänger-) Realität anders aussieht. Trotzdem vielen Dank, ich bleibe dran!
Beitrag erstellt von anonym
Ah, sorry für die Verwirrung: Ja auf dem Studio Code Server in HA ist der SQLite3-Editor tatsächlich nicht verfügbar. Ich werde den Artikel bei Gelegenheit updaten ..
Beitrag erstellt von Bernhard