Zum Thema passend, vielleicht auch ergänzend, Auszüge aus meinen heutigen Diskussion mit Claude. Vorab: Mein Problem war, dass einen meiner 16 Temperatursensoren an der PV-Batterie gesponnen hat (Werte über 80 Grad). Dies war zum Glück Blödsinn. Dies nur kurz zur Erläuterung warum ich nach Werten über 70 Grad gesucht habe.
Meine Frage (erst mal vorsichtig vortasten):
In welchen Tabellen (standard SQlite) werden Sensorwerte angelegt? Eigentlich müssten es doch 3 Tabellen sein. Kurzzeitgedächnis, 5 Min. Aggregation und Langzeitspeicher (1 Std. Aggregation).
Antwort KI:
Deine Intuition ist im Kern richtig, aber es sind genau genommen zwei getrennte Systeme mit insgesamt mehr als drei Tabellen. Der entscheidende Punkt: Nur zwei davon sind wirklich „aggregiert" – das „Kurzzeitgedächtnis" ist es nicht.
1. Die Rohdaten (History) – nicht aggregiert
Das ist dein „Kurzzeitgedächtnis". Hier landet jeder einzelne State-Change genau so, wie er passiert ist – keine Mittelung, kein Min/Max. Beteiligte Tabellen: Home Assistant
-
states – die eigentlichen Zustandswerte (mit metadata_id, last_updated_ts usw.)
-
states_meta – Mapping von entity_id → metadata_id (spart Speicher, da die lange entity_id nicht in jeder Zeile steht)
-
state_attributes – die Attribut-JSONs, dedupliziert
Diese Tabelle wird nach purge_keep_days (Standard 10 Tage) automatisch bereinigt.
2. Die Statistik (Long-Term Statistics) – aggregiert
Hier sitzen deine beiden Aggregations-Stufen:
-
statistics_short_term – die 5-Minuten-Aggregate, berechnet aus der states-Tabelle. Wird ebenfalls nach purge_keep_days (Standard 10 Tage) gepurgt.
-
statistics – die 1-Stunden-Aggregate (Langzeit). Ein stündlicher Aggregat der Short-Term-Statistik wird gespeichert, und diese Tabelle wird nie automatisch gelöscht. Home AssistantHome Assistant
-
statistics_meta – wieder das Metadaten-Mapping für die Statistik-Einträge.
Der Datenfluss ist also gestaffelt: State-Changes werden in der states-Tabelle aufgezeichnet, alle 5 Minuten schreibt die Statistik-Kompilierung nach statistics_short_term, und alle 60 Minuten werden die Short-Term-Werte zu statistics aggregiert. GitHub
Kleine, aber wichtige Korrektur zu deinem Modell
Du hattest gesagt „Kurzzeitgedächtnis / 5-Min-Aggregation / Langzeit (1 Std)". Die Feinheit: Die states-Tabelle ist keine Aggregation – sie ist die vollständige, ungefilterte Rohaufzeichnung. Nur statistics_short_term (5 Min) und statistics (1 Std) sind aggregiert. Die 5-Minuten-Statistik zieht ihre Werte übrigens aus states, nicht umgekehrt.
Ein weiterer Aspekt: Für Statistik-Tabellen werden je nach state_class unterschiedliche Werte gespeichert – bei measurement sind das mean/min/max, bei total/total_increasing dagegen sum und ggf. last_reset_ts.
Nächste Frage (Ok, jetzt mal konkret werden):
Wie lautet der SQL-Befehl wenn ich aus der States-Tabelle für den Sensor “sensor.bat_temp_zelle13” alle Temperaturwerte > 70 Grad löschen möchte?
Hier ist der Befehl – aber ein wichtiger Punkt vorweg: In der aktuellen HA-Schema-Version steht die entity_id nicht mehr direkt in der states-Tabelle. Sie liegt ausgelagert in states_meta und wird über metadata_id verknüpft. Du brauchst also einen Subquery. Außerdem ist die Spalte state ein Text-Feld, weshalb du für den Zahlenvergleich casten musst.
Zuerst prüfen (immer empfehlenswert), was getroffen wird:
SELECT COUNT(*)
FROM states
WHERE metadata_id = (
SELECT metadata_id FROM states_meta
WHERE entity_id = 'sensor.bat_temp_zelle13'
)
AND CAST(state AS REAL) > 70;
Dann das eigentliche Löschen:
DELETE FROM states
WHERE metadata_id = (
SELECT metadata_id FROM states_meta
WHERE entity_id = 'sensor.bat_temp_zelle13'
)
AND CAST(state AS REAL) > 70;
Warum das so aussieht:
-
CAST(state AS REAL) > 70 wandelt den Text in eine Fließkommazahl um. Praktischer Nebeneffekt: Werte wie unavailable oder unknown ergeben beim Cast in SQLite 0.0 und werden dadurch automatisch nicht mitgelöscht.
-
Der Subquery holt sich die passende metadata_id. Wenn du unsicher bist, ob der Sensor evtl. unter mehreren metadata_ids geführt wird (z.B. nach Umbenennung), ersetze = durch IN und lass das SELECT als Liste laufen.
Unbedingt beachten:
- Das löscht die Werte nur aus
states (deinem „Kurzzeitgedächtnis"). Die 5-Minuten- und Stunden-Aggregate in statistics_short_term bzw. statistics bleiben unberührt. Die fehlerhaften 70°C-Ausreißer haben dort ggf. schon die max- oder mean-Werte verfälscht – die musst du separat über Entwicklerwerkzeuge → Statistiken oder direktes SQL auf die Statistik-Tabellen korrigieren.
Wenn du willst, kann ich dir auch die passenden Queries für die beiden Statistik-Tabellen schreiben.
Nächste Frage (man kennt sich nun und kann persönlicher werden):
Spann mich nicht auf die Folter, zeig schon.
Hier sind die passenden Queries. Wichtiger Unterschied vorweg: Die Statistik-Metatabelle ist anders aufgebaut als states_meta. Dort heißt die Spalte mit dem Sensornamen statistic_id (nicht entity_id), und der Primärschlüssel ist id (nicht metadata_id). Wenn du das aus dem states-Query 1:1 übernimmst, findet er nichts.
statistics_short_term (5-Minuten-Aggregate)
Erst prüfen:
SELECT COUNT(*)
FROM statistics_short_term
WHERE metadata_id = (
SELECT id FROM statistics_meta
WHERE statistic_id = 'sensor.bat_temp_zelle13'
)
AND max > 70;
Dann löschen:
DELETE FROM statistics_short_term
WHERE metadata_id = (
SELECT id FROM statistics_meta
WHERE statistic_id = 'sensor.bat_temp_zelle13'
)
AND max > 70;
statistics (1-Stunden-Aggregate / Langzeit)
DELETE FROM statistics
WHERE metadata_id = (
SELECT id FROM statistics_meta
WHERE statistic_id = 'sensor.bat_temp_zelle13'
)
AND max > 70;
Warum max > 70 und nicht state:
Ein Temperatursensor hat state_class: measurement, also stehen hier mean, min und max – kein einzelner Messwert. Die Spalten state und sum sind nur für total/total_increasing-Sensoren gefüllt und bei dir NULL. max > 70 erwischt zuverlässig jede Periode, in der irgendein Wert über 70 lag – auch wenn mean durch den Ausreißer nur leicht angehoben wurde. Würdest du auf mean > 70 filtern, würdest du kurze Spikes übersehen.
Der unvermeidbare Haken bei aggregierten Daten:
Anders als in states kannst du hier den Ausreißer nicht chirurgisch entfernen. Du löschst immer die ganze Periode. Lag in demselben 5-Minuten- bzw. 1-Stunden-Fenster auch ein legitimer Wert (z.B. echte 45°C), verschwindet der mit. Bei einem klaren Fehler (Sensor spuckt kurz 999°C aus) ist das meist verschmerzbar, aber du solltest es wissen.
Zwei weitere Punkte:
-
Home Assistant regeneriert gelöschte Statistik-Perioden nicht automatisch aus states neu – es entstehen also Lücken in den Langzeit-Charts. Das ist normalerweise gewollt (Ausreißer weg), aber erwartbar.
-
Wenn du die betroffenen Zeitpunkte kennst, kannst du zusätzlich zeitlich eingrenzen, um versehentliche Treffer zu vermeiden, z.B. AND start_ts BETWEEN 1700000000 AND 1700086400 (Unix-Timestamps).
by HarryP: