Home Assistant SQL - Integration - Queries

 

In addition to the option of creating template sensors, data can also be read directly from the database via SQL integration. This means, for example, that statistical data can also be queried retrospectively without having to create a corresponding sensor beforehand.

📢 Here's the link to a new post planned: HA - SQlite Insights allow push notifications?

Description SQL query for the SQL integration, "val" is specified as a column in the integration via "as val":
Delta value (production) of an energy sensor yesterday (read from the LTS statistics table) 
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 value (production) of an energy sensor today until now (read from the statistics short_term table) 
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 value (production) of an energy sensor yesterday to the same time (read from the statistics short_term table) 
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 value (production) of an energy sensor yesterday at the same time until the end of the day: e.g. PV production remaining yesterday at the same time (read from the statistics short_term table) 
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 value (production) of an energy sensor yesterday at the same time until the end of the day, read from the States table 
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'));
yesterday: lowest value of a sensor (read from the states table) 
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')
lowest value, e.g. the lowest battery charge status from yesterday. 
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;

Neither Max nor a normal order worked for the charge status of my battery (in percent), as e.g. 100 was interpreted as less than 42.2.
As a solution, I interpreted state as an integer via CAST, sorted it and the smallest value is then output with limit 1.
Battery charge status yesterday at the same time 
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;
last time of a sensor with a certain value as 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;
Last time of a sensor with a specific value as date / time 
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 value (production) of an energy sensor since another sensor last had a certain value (read from the statistics short_term table)

 
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')
Maximum value of a sensor in the last 10 days up to the current time 
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;
Minimum value of a sensor in the last 10 days up to the current time 
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;
Average value of a sensor yesterday 
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'))
Maximum difference of a total sensor: of the last 10 days 
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

The minimum value can be removed by removing "DESC" in order by
Multiple values as a csv string so that this can be further processed in HA in a template. 
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
)
Multiple values combined with the values of a second sensor output as a csv string so that this can be further processed in HA in a template. 
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 blocks for subqueries: everything in sequence

States table (up to 10 days),

see: HA history: more than 10 days? Long Time Statistic (LTS)

Read sensor ID

So that the name of a sensor can be used for the queries described later in this article, here is a simple query for converting the name into the corresponding Entity_id:

Query:

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

Result:

metatdata_id
33616

Query last status from the States table

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

Result:

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

where

  Query
Time later than the current time
 
and strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) > strftime('%H%M%S', datetime('now','localtime'))
Time earlier than the current time 
and strftime('%H%M%S',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%H%M%S', datetime('now','localtime'))
everything before today (until 23:59) on the previous day 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) < strftime('%Y%m%d', datetime('now','localtime'))
today 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime'))
yesterday 
and strftime('%Y%m%d',datetime(last_updated_ts, 'unixepoch','localtime')) == strftime('%Y%m%d', datetime('now','localtime','-1 days'))
Time sunrise today 
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;
Sunset time 
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;
Between sunrise and sunset 
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)
Weather: Cloud coverage yesterday between sunrise and sunset 
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'))

Conclusion:

SQL integration offers a flexible way of accessing databases directly and performing queries without having to create special sensors beforehand. This makes it possible to access statistical data retrospectively. The various SQL queries in the blog post illustrate how specific data sets can be extracted from the tables, be it maximum, minimum or average values of sensors, delta values for energy production or timestamps of certain events. The examples demonstrate how to work with various elements of the SQLite database, including the use of subqueries and the manipulation of date/time formats to precisely match the queries to the desired time frames.

positive Bewertung({{pro_count}})
Rate Post:
{{percentage}} % positive
negative Bewertung({{con_count}})

THANK YOU for your review!

Questions / Comments


By continuing to browse the site, you agree to our use of cookies. More Details