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:

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. Die SQLite-Datenbank kann ĂŒber Linux-Board mitteln mit dem Befehl sqlite ausgelesen und bearbeitet werden.

⚠
Achtung bitte unbedingt ein Backup der Datenbank machen, bevor delete oder Update-Statements ausgefĂŒhrt 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 komfortabler als Sqlite ist das HA-Add-On: SQlite-Web oder beim direkten Zugriff auf das Host-Filesystem VSCode und die VSCode-Extension SQLite-Editor:

FĂŒr das Bearbeiten habe ich nicht den Studio-Code Server in Home-Assistant verwendet, sondern VSCode lokal installiert und eine Remote-Verbindung auf den Host - auf dem der HA-Docker-Container lĂ€uft - aufgebaut. 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

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:

SELECT * FROM "statistics_short_term" where metadata_id = "86"

⚠
Achtung, bevor ein Update-Statement ausgefĂŒhrt wird,
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 = "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

Alte Daten von statistics_short_term wird ohnehin regelmÀssig 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.

⚠
Achtung, bitte vor dieser Aktion unbedingt ein Backup der Datenbank erstellen.
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

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.

positive Bewertung({{pro_count}})
Beitrag bewerten:
{{percentage}} % positiv
negative Bewertung({{con_count}})

DANKE fĂŒr deine Bewertung!

Fragen / Kommentare


(sortiert nach Bewertung / Datum) [alle Kommentare(neueste zuerst)]

✍anonym
14.01.2024 07:25
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

✍anonym
08.11.2023 00:35
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 :-)
↳
✍Bernhard
gepostet am 08.11.2023 07:35
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
↳
✍Bernhard
gepostet am 08.11.2023 07:35
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
↳
✍anonym
gepostet am 08.11.2023 13:40
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
↳
✍Bernhard
gepostet am 08.11.2023 14:47
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

Durch die weitere Nutzung der Seite stimmst du der Verwendung von Cookies zu Mehr Details