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.

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"

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 der bestehenden Werte anhand der STATE-Spalte:

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)

Beispiel

Die Statistik-Daten einer Zigbee-Steckdose haben für SUM bei 0 begonnen, nachdem diese ein paar Monate nicht verwendet wurde. Ein Blick in die Datenbank zeigt das Problem. Anzeige der Daten mittels folgender Query:

select datetime(created_ts, 'unixepoch', 'localtime') AS readabledate, * from statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.oswald_summation_delivered')

Ausgabe:

Wie vorher präsentiert, hab ich zunächst alle SUM-Werte auf null geändert: 

update "statistics"  SET SUM = null where metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.oswald_summation_delivered');

update "statistics"  SET SUM = 0 where rowid IN (SELECT rowid
                FROM statistics
                WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.oswald_summation_delivered')
                LIMIT 1);

WITH meta_id AS (
    SELECT id 
    FROM statistics_meta 
    WHERE statistic_id = 'sensor.oswald_summation_delivered'
),
last_valid AS (
    SELECT SUM, state 
    FROM statistics 
    WHERE metadata_id = (SELECT id FROM meta_id) 
      AND SUM IS NOT NULL 
    ORDER BY id DESC 
    LIMIT 1
),
t (id) AS (
    SELECT rowid 
    FROM statistics 
    WHERE SUM IS NULL 
      AND metadata_id = (SELECT id FROM meta_id)
)
UPDATE statistics 
SET SUM = (
    (SELECT SUM FROM last_valid) + 
    (STATE - (SELECT state FROM last_valid))
)
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 für das Korrigieren der Langzeitstatistik anzupassen, siehe: HA Verlauf: mehr als 10 Tage? Long Time Statistic (LTS). Wer die Daten in statistics_short_term dennoch anpassen will, muss in den vorherigen Queries lediglich statistics mit statistics_short_term ersetzen.

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.

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

DELETE FROM statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');

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);

SELECT * FROM statistics where metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');

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');

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;

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');

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'))

Wetter: shared_attrs nach LTS Sensor

Um die LTS-Daten für einen neuen Sensor für den Bewölkungsgrad vorab schon mal mit den States der letzten 10 Tage zu befüllen, habe folgende Queries verwendet:

1) auslesen: 

SELECT 
  strftime('%Y-%m-%d %H:00:00', s.last_updated_ts, 'unixepoch') as hour_start,
  AVG(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as mean,
  MIN(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as min,
  MAX(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as max
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
JOIN state_attributes sa ON s.attributes_id = sa.attributes_id
WHERE sm.entity_id = 'weather.home'
  AND json_extract(sa.shared_attrs, '$.cloud_coverage') IS NOT NULL
GROUP BY hour_start
ORDER BY hour_start DESC;

2) ID des Sensors:

SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.cloud_coverage';

metadata_id in der folgenden Query anpassen und starten: an 2 Stellen (?? ersetzen)

INSERT INTO statistics (created_ts, start_ts, state, mean, min, max, metadata_id)
SELECT 
  strftime('%s', 'now') as created_ts,        
  strftime('%s', hour_start) as start_ts,      
  mean as state,                                
  mean, 
  min, 
  max,
  ?? as metadata_id                        
FROM (
  SELECT 
    strftime('%Y-%m-%d %H:00:00', s.last_updated_ts, 'unixepoch') as hour_start,
    AVG(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as mean,
    MIN(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as min,
    MAX(CAST(json_extract(sa.shared_attrs, '$.cloud_coverage') AS FLOAT)) as max
  FROM states s
  JOIN states_meta sm ON s.metadata_id = sm.metadata_id
  JOIN state_attributes sa ON s.attributes_id = sa.attributes_id
  WHERE sm.entity_id = 'weather.home'
    AND json_extract(sa.shared_attrs, '$.cloud_coverage') IS NOT NULL
  GROUP BY hour_start
)
WHERE start_ts NOT IN (SELECT start_ts FROM statistics WHERE metadata_id = ??);

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.