Benachrichtigungen
Alles löschen

Mit SQL noch mehr Daten in Home Assistant nutzen

21 Beiträge
4 Benutzer
0 Reactions
933 Ansichten
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  
wpf-cross-image

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…


   
AntwortZitat
(@Gunter)
Active Member Gast
Beigetreten: Vor 2 Jahren
Beiträge: 6
 

Hallo Olli,

ich denke im SQL Code für SQLite ist ein Copy/Past Fehler 😉

... FROM statistics...
... weitere ...

Viele Grüße
Gunter


   
AntwortZitat
(@Gunter)
Active Member Gast
Beigetreten: Vor 2 Jahren
Beiträge: 6
 

Jetzt hat die Formularfunktion den Text "..." entcodet 😉


   
AntwortZitat
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  

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


   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

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.

grafik

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


   
AntwortZitat
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  

@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.).


   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

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

grafik

Input_select und Sensoren auf HA

grafik

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

 

 


   
AntwortZitat
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  

@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.


   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

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.


   
AntwortZitat
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  

@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 DESC 

Durch 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!


   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

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.


   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

Hallo 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
grafik

   
AntwortZitat
 Olli
(@olli)
Mitglied Admin
Beigetreten: Vor 10 Jahren
Beiträge: 760
Themenstarter  

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_ts

   
AntwortZitat
(@jove02)
Active Member
Beigetreten: Vor 6 Monaten
Beiträge: 6
 

Hallo Olli

vielen Dank, das war es

Jürgen


   
AntwortZitat
(@markus)
Active Member
Beigetreten: Vor 5 Monaten
Beiträge: 5
 

Hallo Olli,

ich habe zwei Anwendungsfälle, die ich separat poste.

Anwendungsfall 1: Ich lese meinen Stromzähler mit einen IR Kopf und Tibber aus. Beide Zählerstände landen in HA. Aus mir nicht erklärlichen Gründen, waren für die Einspeisewerte vom Zähler nur die letzten 3 oder 4 Monate in HA. Die Tibber Werte waren aber durchgängig vorhanden.

Hast Du eine Idee, wie man das versehentlich hinbekommt? Im SQLite Web habe ich bewusst nur Select Statements ausgeführt, aber viel in den Developertools HA unter die Haube geschaut.

Die Werte vom Tibber (Medadata_id 179) habe ich über INSERT INTO auf den Zähler (Metadata_id 168) übertragen. Da noch Werte vorhanden waren, nur die vor dem ersten start_ts Eintrag für ID 168

INSERT INTO statistics (created,start, mean, min, max, last_reset, state, sum, metadata_id, created_ts, start_ts, last_reset_ts)
SELECT
  created,
  start,
  mean,
  min,
  max,
  last_reset,
  state,
  sum,
  168 AS metadata_id,
  created_ts,
  start_ts,
  last_reset_ts
FROM statistics
WHERE metadata_id = 179 AND start_ts < 1765400000.0 AND true

Grüße

Markus


   
AntwortZitat
Seite 1 / 2