Im Hintergrund speichert Home Assistant Daten, Zustände und die Verläufe deiner Entitäten in Datenbanken. Standardmäßig kommt dafür SQLite zum Einsatz. Falls du schon länger Home Assistant nutzt, hast du vielleicht irgendwann auf eine extern gehostete Datenbank wie MariaDB umgestellt. Wenn du an der Datenbank deines Home Assistants nichts verändert hast, verwendest du sehr wahrscheinlich SQLite. Auf diese Daten kann man direkt zugreifen, wenn man ein wenig SQL beherrscht.
Um manuell über die Befehlszeile mit der SQLite-Datenbank zu arbeiten, benötigst du eine Installation von sqlite3 (Installationsanleitung). Alternativ kannst du den DB Browser for SQLite nutzen. Damit erhältst du einen praktischen Viewer zum Erkunden der Daten und einen Editor für SQL-Befehle. In meinem Video zeige ich dir, wie du das Addon SQLite Web verwendest, das du über den Addon-Store installieren kannst – vorausgesetzt, du betreibst Home Assistant auf einem Raspberry Pi.
Je nachdem, ob du SQLite oder MariaDB verwendest, gibt es kleine Unterschiede in der SQL-Syntax. Achte also darauf, welchen Code-Schnipsel du kopierst/nutzt!
Bedenke außerdem, dass du mit SQL-Befehlen auch Dinge kaputt machen kannst. Kopiere also nicht einfach irgendwelche SQL-Befehle, die du online findest, ohne zu verstehen, was sie tun. Die von mir bereitgestellten SQL-Befehle sind Select-Abfragen. Damit kannst du nichts kaputt machen – sie lesen lediglich Daten aus.
In meinem Video erkläre ich dir konkret, wie du für eine Energie-Entität den Verbrauch über einen beliebigen Zeitraum berechnest. Dazu wird per SQL-Select der Zustand der Entität am Ende des Zeitraums ermittelt und der Zustand zu Beginn des Zeitraums abgezogen. Die Differenz ergibt dann den Zuwachs in diesem Zeitraum.
Du magst Home Assistant? Dann abonniere kostenlos meine Beiträge mit Tipps, Tricks und Anleitungen rund um Home Assistant:
Garantiert kein Spam, keine Werbung und immer mit Abmelde-Link, solltest du es dir anders überlegen!
Du musst dabei natürlich die Entität in meinem Code (sensor.shellyplug_s_dcbaf7_energy) durch deine eigene Entität ersetzen. Ebenso kannst (und musst) du die Timestamps anpassen: für den Beginn des Zeitraums (‚2024-09-01 00:00:00‘) und für das Ende des Zeitraums (‚2024-12-01 00:00:00‘).
SQL-Code aus dem Video für MariaDB:
SELECT ROUND((SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.shellyplug_s_dcbaf7_energy' AND created_ts < UNIX_TIMESTAMP('2024-12-01 00:00:00') ORDER BY created_ts DESC LIMIT 0,1) - (SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.shellyplug_s_dcbaf7_energy' AND created_ts > UNIX_TIMESTAMP('2024-09-01 00:00:00') ORDER BY created_ts ASC LIMIT 0,1),2) AS wert
SQL-Code aus dem Video für SQLite (Home Assistant Default):
SELECT ROUND((SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.daily_shelly_energy' AND created_ts < strftime('%s','2024-12-01 00:00:00') ORDER BY created_ts DESC LIMIT 0,1) - (SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.daily_shelly_energy' AND created_ts > strftime('%s','2024-09-01 00:00:00') ORDER BY created_ts ASC LIMIT 0,1),2) AS wert
Weitere SQL-Befehle für Home Assistant
Du brauchst einen anderen SQL-Befehl um individuelle Daten zu ermitteln? Schreibe was du benötigst als Kommentar und ich versuche sehr gerne dir dabei zu helfen. Solche SQL-Befehle werde ich dann auf dieser Seite für alle anderen ergänzen!
Produktempfehlungen
Hallo Olli
vielen Dank, das war es
Jürgen
Versuche mal folgenden Code um created_ts zu transformieren:
SELECT mean, DATETIME(created_ts, 'auto') FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.tibber_status_solvis_sg_ready_num' AND created_ts > strftime('%s','2025-02-04 00:00:00') ORDER BY created_tsHallo Olli
jetzt benötige ich bitte doch nochmals deine Unterstützen beim Anzeigen „ts“ im gängigen Format
SELECT mean, datetime(created_ts) FROM statistics INNER JOIN statistics_meta
ON statistics.metadata_id=statistics_meta.id
WHERE statistics_meta.statistic_id=’sensor.tibber_status_solvis_sg_ready_num‘ AND
created_ts > strftime(‚%s‘,’2025-02-04 00:00:00′) ORDER BY created_ts
Im Add On habe ich auf Basis deiner Beispiele ein erstes Query gebastelt. Dabei ist mir aufgefallen, dass für den Template-Sensor keine Statistik in der DB war. Ursache ein fehlendes „state_class: measurement“.
Jetzt kann ich natürlich darüber rätseln, ob das die Ursache für den falschen Wert in der InflzxDB ist.
Voreinmal vielen Dank für deine Unterstützung.
Ich hatte wohl die Integration SQL installiert und nicht das Addon. Aber auch damit stoße ich an meine Grenzen, denn die relationale DB ist ohne strukturkenntnisse mal nicht eben pre Klick abzufragen. Wie ich das sehe, muss der Select über mehrere Tabellen gehen.
Daher nehme ich gerne dein Angebot tur Unterstützung beim Select an.
@jove02 Probier mal folgenden Select aus:
SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id='sensor.tibber_status_solvis_sg_ready_num' AND created_ts > strftime('%s','2025-02-04 12:09:00') AND created_ts < strftime('%s','2025-02-04 13:00:00') ORDER BY created_ts DESCDurch die Angaben „created_ts > …“ und „created_ts < …“ sollte die Ergebnisliste deinem XLS-Screenshot von weiter ohne entsprechen bzw. den auffälligen Eintrag mit „3“ beinhalten.
Wenn du in der DB arbeitest, beachte, dass du keine Inserts, Updates oder Dinge in die Richtung machst, da du damit dein HA „beschädigen“ kannst. SELECT-Abfragen stellen hingegen kein Problem dar!
Hallo Olli
vielen Dank für die schnelle Antwort.
Leider total verstanden.
Auszug aus InluxDB. Grau der Eintrag mit 3, der eigentlich nicht vorkommen kann
Input_select und Sensoren auf HA
Man sieht zur InfluxDB die Abweichung, die ich versuche zu ergründen. Dazu wollte ich mit SQL auf die HA-DB zugreifen, denn es muss in HA den Wert 3 geben, wo sollte er sonst herkommen, und das nicht nur einmal.
Daher meine bitte, kannst du einmal den obigen SELECT prüfen.
Danke
@jove02 Danke für die weiteren Details!
Was ich in dem Video und Blogbeitrag beschreibe, ist wie man eine Wertentwicklung einer Entität durch das „vergleichen“ zweier Werte generiert (also Wert a (neu) abzüglich Wert b (alt) ergibt die Wertentwicklung).
Was du benötigst ist vollkommen anders gelagert und du wirst das anhand meines Beispiels nur mit unnötig viel Aufwand hinbekommen (also möglich ist es sicherlich, aber alles andere als pragmatisch).
An deiner Stelle würde ich mir direkten Zugriff auf die DB verschaffen um dort direkt (also ohne den Umweg über HA) Selects absetzen zu können. Das beschreibe ich in dem Video auch (https://youtu.be/qe8Syxfo-LY?si=5XVF19W1qKPfglIC&t=886). Wenn du direkten Zugriff auf die DB hast, kann ich dir gerne mit dem Select behilflich sein.
Hallo Olli
Über Grafana werden über eine InfluxDB für einen Sensor Werte angezeigt, die nicht sein können, da ich die Daten über ein input_select fülle.
Gefüllt wird dieser input_select über eine Automatisierung (theoretisch auch manuell) und dann in einen Sensor übernommen und in zweiten Sensor nur die 1. Stelle.
– name: Tibber Status Solvis SG-Ready
state: >-
{{states(‚input_select.solvis_sg_ready‘) }}
unique_id: tibber_status_solvis_sg_ready
– name: Tibber Status Solvis SG-Ready num
state: >-
{{states(‚input_select.solvis_sg_ready‘)|truncate(1, true, “) }}
unique_id: tibber_status_solvis_sg_ready_num
Bei bestimmten Schaltvorgängen sehe ich in Grafana an stelle des von 4 auf 2 geschalteten Wertes aber 3 (einmal sogar 3,5 ???)
Die Anzeige der Sensoren in HA zeigen doch immer 2 an.
Um dieses Verhalten aufzuspüren wollte ich direkt in die DB schauen und habe deinen Beitrag gefunden. Doch meine Anpassung deines Beispiels erzeugt kein Ergebnis
SELECT ROUND((SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id=’sensor.tibber_status_solvis_sg_ready_num‘ AND created_ts < strftime(‚%s‘,’2025-02-04 14:00:00′) ORDER BY created_ts DESC LIMIT 0,1) – (SELECT state FROM statistics INNER JOIN statistics_meta ON statistics.metadata_id=statistics_meta.id WHERE statistics_meta.statistic_id=’sensor.tibber_status_solvis_sg_ready_num‘ AND created_ts > strftime(‚%s‘,’2025-02-04 11:00:00′) ORDER BY created_ts ASC LIMIT 0,1),2) AS wert
(leider klappt das hier nicht mit Zeilenumbrüchen)
Siehst du evtl den Fehler in meinem Select?
Danke
@jove02 Hi! Aus der Ferne auf jeden Fall nicht ganz so easy, ich bin mir nicht sicher, ob ich es richtig verstanden habe. Aber hast du mal ein wenig mit den Zeiten gespielt? Je nach Setup ist ein Offset nicht ungewöhnlich (i. d. R. 1-2 Stunden bspw.).
Hi Gunter, du hast vollkommen Recht – sorry für den Fehler! Ich habe es im Beitrag korrigiert! Vielen Dank für deinen Hinweis!
Viele Grüße
Olli
Jetzt hat die Formularfunktion den Text „
...“ entcodet 😉Hallo Olli,
ich denke im SQL Code für SQLite ist ein Copy/Past Fehler 😉
… FROM
statistics…… weitere
...Viele Grüße
Gunter