Home Assistant SQL - Integration

 

Neben der Möglichkeit Template-Sensoren zu erstellen, können Daten auch direkt über die SQL-Integration aus der Datenbank gelesen werden. So können zum Beispiel Statistik-Daten auch im Nachhinein abgefragt werden, ohne zuvor einen entsprechenden Sensor anlegen zu müssen.

📢 Hier ist der Link zu einem neuen Beitrag geplant: HA - SQlite Insights Push-Nachrichten erlauben?

Beschreibung SQL-Query für die SQL-Integration, als Spalte in der Integration wird "val" über "as val" vorgegeben: 
Delta-Wert (Produktion) eines Energy-Sensors gestern (aus der LTS-Statistik-Tabelle gelesen) 
SELECT (Max(state) - Min(state)) as val FROM statistics 
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.household_energy')
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
Delta-Wert (Produktion) eines Energy-Sensors heute bis jetzt (aus der Statistik-Short_Term-Tabelle gelesen) 
SELECT (Max(state) - Min(state)) as val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.household_energy')
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime'))
Delta-Wert (Produktion) eines Energy-Sensors gestern bis zur selben Zeit (aus der Statistik-Short_Term-Tabelle gelesen) 
SELECT (Max(state) - Min(state)) as val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.household_energy')
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
AND strftime('%H%M%S',datetime(created_ts, 'unixepoch','localtime')) < strftime('%H%M%S', datetime('now','localtime'))
Delta-Wert (Produktion) eines Energy-Sensors gestern zur selben Zeit bis Tagesende: z.B. PV-Produktion verbleibend gestern zur selben Zeit (aus der Statistik-Short_Term-Tabelle gelesen) 
SELECT (Max(state) - Min(state)) as val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.pv_panels_energy') 
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d', datetime(created_ts, 'unixepoch', 'localtime')) == strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))
AND strftime('%H%M%S', datetime(created_ts, 'unixepoch', 'localtime')) > strftime('%H%M%S', datetime('now', 'localtime', '-1 days'));
Delta-Wert (Produktion) eines Energy-Sensors gestern zur selben Zeit bis Tagesende, aus der States-Tabelle gelesen 
SELECT (Max(state) - Min(state)) as val 
FROM states  
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.pv_panels_energy')
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) == strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))
AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) > strftime('%H%M%S', datetime('now', 'localtime'));
gestern: kleinster Wert eines Sensors (aus der States-Tabelle gelesen) 
SELECT MIN(state) AS val 
FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.byd_available_capacity') 
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y-%m-%d', last_updated_ts, 'unixepoch', 'localtime') = strftime('%Y-%m-%d', 'now', 'localtime', '-1 day')
kleinster Wert, z. B. der tiefste Batterieladestatus von gestern. 
SELECT CAST(state AS INTEGER) AS val 
FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.byd_battery_box_premium_hv_ladezustand') 
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
ORDER BY val 
LIMIT 1;

Für den Ladezustand meiner Batterie (in Prozent) funktionierte weder Max, noch ein normales Order, da z.B. 100 kleiner als 42.2 interpretiert wurde.
Als Lösung habe ich state über CAST als Integer interpretiert, sortiert und mit limit 1 wird dann der kleinste Wert ausgegeben.
Batterieladestatus gestern zur selben Zeit 
SELECT state as val 
FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.byd_battery_box_premium_hv_ladezustand') 
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) > strftime('%H%M%S', 'now', 'localtime')
ORDER BY last_updated_ts 
LIMIT 1;
letzter Zeitpunkt eines Sensors mit einem bestimmten Wert als Unix Timestamp 
SELECT last_updated_ts AS val 
FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.fronius_storage_chast') 
AND state = 'FULL'  
ORDER BY last_updated_ts DESC 
LIMIT 1;
letzter Zeitpunkt eines Sensors mit einem bestimmten Wert als Datum / Uhrzeit 
SELECT datetime(s.last_updated_ts, 'unixepoch', 'localtime') as val 
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.fronius_storage_chast' 
AND s.state = "FULL"
ORDER BY s.last_updated_ts DESC 
LIMIT 1

Delta-Wert (Produktion) eines Energy-Sensors seit dem ein anderen Sensors zuletzt einen bestimmten Wert hatte (aus der Statistik-Short_Term-Tabelle gelesen)

 
WITH last_full_ts AS (
    SELECT last_updated_ts 
    FROM states 
    WHERE metadata_id = (SELECT metadata_id FROM states_meta 
                         WHERE entity_id = 'sensor.fronius_storage_chast') 
      AND state = 'FULL'
    ORDER BY last_updated_ts DESC 
    LIMIT 1
)

SELECT (MAX(state) - MIN(state)) AS val 
FROM statistics_short_term 
WHERE metadata_id = (SELECT id 
                     FROM statistics_meta 
                     WHERE statistic_id = 'sensor.household_energy') 
  AND created_ts BETWEEN (SELECT last_updated_ts FROM last_full_ts) 
                      AND unixepoch('now')
Maximalwert eines Sensors in den letzten 10 Tagen bis zur aktuellen Uhrzeit 
SELECT 
    MAX(CAST(s.state AS FLOAT)) AS val,  
    datetime(s.last_updated_ts, 'unixepoch', 'localtime') AS last_updated
FROM 
    states s
JOIN 
    states_meta sm ON s.metadata_id = sm.metadata_id
WHERE 
    sm.entity_id = 'sensor.pv_panels_energy_today' 
    AND s.state NOT IN ('unknown', 'unavailable')
    AND strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%H%M%S', datetime('now','localtime'))
GROUP BY 
    last_updated;
Mindestwert eines Sensors in den letzten 10 Tagen bis zur aktuellen Uhrzeit 
SELECT 
    MIN(CAST(state AS FLOAT)) AS val,  
    datetime(last_updated_ts, 'unixepoch', 'localtime') AS last_updated
FROM 
    states 
WHERE 
    metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.pv_panels_energy_yesterday_remaining') 
    AND state NOT IN ('unknown', 'unavailable') 
    and strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%H%M%S', datetime('now','localtime'))
GROUP BY 
    last_updated;
Durchschnittswert eines Sensors gestern 
SELECT AVG(state) AS val 
FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.cloud_coverage_daylight') 
AND state NOT IN ("unknown", "unavailable", "") 
AND strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
maximaler Unterschied eines Summen-Sensors: der letzten 10 Tage 
WITH RECURSIVE dates(date) AS (
    SELECT datetime('now', '-1 day','localtime')
    UNION ALL
    SELECT datetime(date, '-1 day','localtime')
    FROM dates
    WHERE date > datetime('now', '-10 day','localtime')
)SELECT strftime('%Y%m%d',date), (Max(state) - Min(state)) as val 
FROM states, dates
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.pv_panels_energy')
AND state NOT IN ('unknown', '', 'unavailable')
AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) == strftime('%Y%m%d', date)
GROUP BY date order by val DESC limit 1

Der Minimalwert kann durch das Entfernen von "DESC" in order by entfernt werden
Mehrere Werte als csv-String, damit dieser in HA in einem Template weiterverarbeitet werden kann. 
SELECT 
    GROUP_CONCAT(localdate || ":" || state, ",") AS val, 
    localdate 
FROM (
    SELECT 
        ROUND(state, 2) AS state,
        DATE(datetime(last_updated_ts, 'unixepoch', 'localtime')) AS localdate 
    FROM 
        states 
    WHERE 
        metadata_id = (
            SELECT metadata_id 
            FROM states_meta 
            WHERE entity_id = 'sensor.pv_panels_energy_yesterday'
        ) 
        AND state NOT IN ("unknown", "", "unavailable") 
        AND CAST(strftime('%H%M', datetime(last_updated_ts, 'unixepoch', '+2 hours')) AS FLOAT) > CAST(strftime('%H%M', date('now', '+2 hours')) AS FLOAT) 
    GROUP BY 
        localdate 
    ORDER BY 
        localdate DESC
)
Mehrere Werte kombiniert mit den Werten eines zweiten Sensors ausgegeben als csv-String, damit dieser in HA in einem Template weiterverarbeitet werden kann. 
WITH energy_data AS (
    SELECT 
        date(datetime(h.last_updated_ts, 'unixepoch', 'localtime'), 'localtime', '-1 days') AS localdate,
        round(h.state, 2) AS energy
    FROM states h
    WHERE 
        metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.pv_panels_energy_yesterday') 
        AND state NOT IN ("unknown", "", "unavailable")
        AND CAST(strftime('%H%M', datetime(last_updated_ts, 'unixepoch', '+121 minutes')) AS FLOAT) > CAST(strftime('%H%M', date('now', '+121 minutes')) AS FLOAT)
    GROUP BY localdate
),
cloud_coverage_data AS (
    SELECT 
        date(datetime(c.last_updated_ts, 'unixepoch', 'localtime'), 'localtime', '-1 days') AS localdate,
        round(c.state, 2) AS cloudcoverage
    FROM states c
    WHERE 
        metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.cloud_coverage_daylight_yesterday_avg') 
        AND state NOT IN ("unknown", "", "unavailable")
        AND CAST(strftime('%H%M', datetime(last_updated_ts, 'unixepoch', '+2 hours')) AS FLOAT) > CAST(strftime('%H%M', date('now', '+2 hours')) AS FLOAT)
)
SELECT 
    GROUP_CONCAT(REPLACE(e.localdate, "-", "") || ":" || e.energy || ":" || c.cloudcoverage, ",") AS val
FROM 
    energy_data e
LEFT JOIN 
    cloud_coverage_data c ON e.localdate = c.localdate
ORDER BY 
    e.localdate DESC;

SQL Blöcke für Subqueries: alles der Reihe nach

States-Tabelle (bis zu 10 Tage),

siehe: HA Verlauf: mehr als 10 Tage? Long Time Statistic (LTS)

Sensor-ID auslesen

Damit für die später in diesem Artikel beschriebenen Abfragen der Name eines Sensors für die Queries verwendet werden kann, vorab eine einfache Abfrage für das Umwandeln des Namens in die entsprechende Entity_id:

Query:

SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.timestamp'

Ergebnis:

metatdata_id
33616

Query letzter Status aus der States Tabelle

SELECT state, datetime(last_updated_ts, 'unixepoch','localtime') as val FROM states 
WHERE metadata_id = (SELECT metadata_id FROM states_meta 
WHERE entity_id = 'sensor.water_timestamp')  and state != "unknown" and 
state != "unavailable" and state != "" order by last_updated_ts desc limit 1

Ergebnis:

val localtime
2024-09-22T13:46:28+00:00 2024-09-22 15:47:06

where

  Query
Uhrzeit später als die aktuelle
 
and strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) > strftime('%H%M%S', datetime('now','localtime'))
Uhrzeit früher als die aktuelle 
and strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%H%M%S', datetime('now','localtime'))
alles vor heute (bis 23:59) am Vortag 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%Y%m%d', datetime('now','localtime'))
heute 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime'))
gestern 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
Uhrzeit Sonnenaufgang heute 
SELECT 
    strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS time 
FROM 
    states
WHERE 
    metadata_id = (
        SELECT metadata_id 
        FROM states_meta
        WHERE entity_id = 'sun.sun'
    ) 
    AND state = 'above_horizon' 
    AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) >= strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))
ORDER BY 
    time  
LIMIT 1;
Uhrzeit Sonnenuntergang 
SELECT strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS time
FROM states
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sun.sun')
    AND state = 'above_horizon'
    AND last_updated_ts >= strftime('%s', 'now', 'localtime', '-1 days')
ORDER BY last_updated_ts DESC
LIMIT 1;
Zwischen Sonnaufgang und Sonnenuntergang 
WITH sun_times AS (
    SELECT 
        strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS time
    FROM states
    WHERE 
        metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sun.sun')
        AND state = "above_horizon"
        AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) >= strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))
),
first_sun_time AS (
    SELECT time FROM sun_times ORDER BY time LIMIT 1
),
last_sun_time AS (
    SELECT time FROM sun_times ORDER BY time DESC LIMIT 1
)
SELECT 
    state AS val, 
    datetime(last_updated_ts, 'unixepoch', 'localtime') AS datetime 
FROM 
    states
WHERE 
    metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.grid_timestamp')
    AND state != "unknown"
    AND state != "unavailable"
    AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) > (SELECT time FROM first_sun_time)
    AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) <= (SELECT time FROM last_sun_time)
Wetter: Cloud Coverage gestern zwischen Sonneaufgang und Sonnenuntergang 
WITH sun_times AS (
    SELECT 
        strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS time
    FROM states
    WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sun.sun')
      AND state = 'above_horizon'
      AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) >= strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))
    ORDER BY last_updated_ts
),
sunrise AS (
    SELECT time FROM sun_times ORDER BY time LIMIT 1
),
sunset AS (
    SELECT time FROM sun_times ORDER BY time DESC LIMIT 1
)
SELECT 
    state AS val, 
    datetime(last_updated_ts, 'unixepoch', 'localtime') AS datetime
FROM states
WHERE metadata_id = (SELECT metadata_id FROM states_meta WHERE entity_id = 'sensor.weather_cloud_coverage')
  AND state NOT IN ('unknown', '', 'unavailable')
  AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) > (SELECT time FROM sunrise)
  AND strftime('%H%M%S', datetime(last_updated_ts, 'unixepoch', 'localtime')) <= (SELECT time FROM sunset)
  AND strftime('%Y%m%d', datetime(last_updated_ts, 'unixepoch', 'localtime')) = strftime('%Y%m%d', datetime('now', 'localtime', '-1 days'))

Fazit:

Die SQL-Integration bietet eine flexible Möglichkeit, direkt auf Datenbanken zuzugreifen und Abfragen durchzuführen, ohne zuvor spezielle Sensoren zu erstellen. Dies erlaubt es, auch nachträglich auf statistische Daten zuzugreifen. Die verschiedenen SQL-Abfragen im Blogbeitrag veranschaulichen, wie spezifische Datensätze aus den Tabellen herausgezogen werden können, sei es maximaler, minimaler oder durchschnittlicher Wert von Sensoren, Delta-Werte für Energieproduktion oder Zeitstempel bestimmter Ereignisse. Die Beispiele demonstrieren den Umgang mit verschiedenen Elementen der SQLite-Datenbank, inklusive der Nutzung von Subqueries und der Manipulation von Datums-/Zeitformaten, um die Abfragen präzise an die gewünschten Zeitrahmen anzupassen.

 

positive Bewertung({{pro_count}})
Beitrag bewerten:
{{percentage}} % positiv
negative Bewertung({{con_count}})

DANKE für deine Bewertung!

Fragen / Kommentare


Durch die weitere Nutzung der Seite stimmst du der Verwendung von Cookies zu Mehr Details