Um bessere SQL-Abfragen zusammenstellen zu können, habe ich mir das Datenbankschema von Home-Assistant näher angesehen. Zunächst hauptsächlich um bestimmte historische Daten zu korrigieren, später um die Energievorhersage meiner PV-Anlage zu verbessern, was eine effizientere Steuerung der Heizung ermöglicht. Details zu den Abfragen für die PV-Prognose folgen in einem gesonderten Artikel..

Mittels SQL-Query können direkte Abfragen oder Änderungen an der Datenbank vorgenommen werden:

Siehe auch die unterschiedlichen Möglichkeiten für einen direkten Zugriff auf dei Datenbank: 3 Varianten: SQLite Datenbank Zugriff - Home Assistant.

Ein Blick in SQLite3 Editor in VSCode, zeigt die einzelnen Tabellen und deren Beziehungen.

Interessant für aktuelle Daten innerhalb von 10 Tagen ist die Tabelle "states". Die Tabelle "statistics" liefert auch ältere Daten: Pro Stunde einen Wert. 

States- Daten (aktuelle Werte aller Entitäten)

Die eindeutigen Kennungen (entity_id) aller Entitäten sind in der Tabelle "states_meta" gelistet:

Die eigentlichen Werte der Entitäten werden in der Tabelle "states" gespeichert, darin unter Verwendung der metadata_id:

Die gelisteten Werte in diesem Beispiel gehören also alle zu "sensor.pv_spannung_dc", da die metadata_id "20" verwendet wird und diese in states_meta auf "sensor.pv_spannung_dc" übersetzt wird. Die attributes_id verweist auf einen zusätzlichen Eintrag in der Tabelle "state_attributes". Über eine angepasste SQL-Abfrage können die Daten der 3 Tabelle zusammengefasst und in einem lesbaren Format ausgegeben werden:

Letzte Stunde, alle Status - Daten aller Entitäten inklusive entity_id und shared_attrs: 

Folgende Query listet sämtliche Statusinformationen aller Entitäten der letzten Stunde:

SELECT STRFTIME('%Y.%m.%d %H:%M', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS date,
    states_meta.entity_id,
    states.state,
    shared_attrs
FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE last_updated_ts BETWEEN strftime('%s', 'now', '-1 hour') AND strftime('%s', 'now')

Aktueller Status für eine bestimmte Entität

SQL-Query:

select datetime(last_updated_ts, 'unixepoch', 'localtime') AS readabledate,state from states where metadata_id = ( SELECT metadata_id FROM states_meta
WHERE entity_id = 'sensor.flowmeter_sum')  order by last_updated_ts desc limit 10;

Ausgabe:

Aktueller Status für eine bestimmte Entität inklusive shared_attrs

Zusätzlich zum aktuellen Status (state) besitzen bestimmte Entitäten zusätzliche Eigenschaften, abgelegt in einer zusätzlichen Spalte: shared_attrs. Für das Auslesen einer Entität und deren zusätzlichen Eigenschaften (shated_attrs) kann die folgende Query verwendet werden:

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id = 'sensor.fronius_storage_chast' order by last_updated_ts DESC limit 1

Ausgabe:

Status einer bestimmten Entität zu einem bestimmten Zeitpunkt, als Beispiel gestern zur selben Zeit

SQL-Query

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as date,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id = 'sensor.fronius_storage_chast' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-1 days')) order by last_updated_ts DESC limit 1

Ausgabe:

Entitäten mit zusätzlichen Daten in shared_attrs: z.B. Wetterdaten

Die folgende Abfrage ruft die letzten zehn Datensätze zur Entität "weather.home" ab. Enthalten sind das formatierte Datum, die Entitäts-ID, der Zustand und gemeinsame Attribute aus der shared_attrs- Tabelle. Die Ergebnisse wurden in folgender SQL-Abfrage nach dem letzten Aktualisierungszeitpunkt sortiert:

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id like '%weather.home' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-0 days')) order by last_updated_ts DESC limit 10

Ausgabe:

Um bestimmte Werte aus der Spalte shared_attrs als eigene Spalten anzuzeigen, können die JSON-Daten mit folgender SQL-Abfrage extrahiert werden: 

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,json_extract(shared_attrs, '$.temperature') AS temperature,json_extract(shared_attrs, '$.cloud_coverage') AS cloud_coverage, shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id like '%weather.home' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-0 days')) order by last_updated_ts DESC limit 10

Ausgabe:

Historische Daten: Werte von den Statistik-Tabellen

Wie auch die Status-Tabelle verwenden die Statistik-Tabellen nicht direkt die eindeutige Entitäts-ID (z.B. sensor.temperatur), sondern eine Meta-Tabelle (statistics_meta) für die Übersetzung der Eintitäts-ID (Spalte: statistic_id) auf eine id (Nummer)

Alternativ zur Status-Tabelle (states), können die Werte einer bestimmten Entität über eine SQL-Query aus der Tabelle "statistics" gelesen werden. Die metatdata_id wird auch hier über eine Sub-Query aus der Tabelle statistics_meta übersetzt:

Datenbank Query:

select datetime(created_ts, 'unixepoch', 'localtime') AS readabledate, * from statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.flowmeter_sum') AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) > strftime('%Y%m%d', datetime('now','localtime','-3 days'));

Ausgabe:

Die Statistik-Tabelle hat eine weitere Besonderheit: Abhängig vom Sensor-Typ werden unterschiedliche Spalten der Datenbanktabelle verwendet: Sensoren des Typs state_class=total oder total_increasing speichern deren Werte in den Spalten: "state" und "sum". Ein Sensor des Typs "state_class = measurement" verwendet die Spalten mean, min und max.

Hier die eine SQL-Query für einen Sensor des Typs: "state_class = measurement":

select datetime(created_ts, 'unixepoch', 'localtime') AS readabledate,* from statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.eg_temperatur') AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) > strftime('%Y%m%d', datetime('now','localtime','-3 days'));

Ausgabe:

Die Tabelle "statistics" speichert stündliche Werte, ohne diese zu löschen. Anders die Tabelle statistics_short_term: diese speichert die Werte im 5-Minuten-Takt und löscht die Daten, wie auch die "states"-Tabelle nach 10 Tagen. Vom Datenbank-Layout ist die Tabelle statistics und statistics_short_term ident. Beide verwenden die Tabelle statistics_meta und beide befüllen abhängig vom Sensor-Typ state und sum oder mean, min und max.

Als Beispiel werden für die Detailansicht eines Sensors für: Minimum, Mittel und Maximum die Daten aus den Spalten "mean", "min" und "max" der Tabelle "statistics_short_term" verwendet:

Fazit

Das Home-Assistant Datenbankschema erlaubt eine effiziente Auswertung von Entitätsdaten durch die Verwendung von Tabellen für aktuelle und historische Informationen. Die Tabelle "states" bietet Zugang zu aktuellen Daten, während "statistics" für Langzeitdaten mit stündlichem Intervall genutzt wird. Entitäten und ihre zusätzlichen Attribute können durch SQL-Abfragen zielgerichtet und individuell ausgewertet werden.