I’m trying to update my SQL sensor queries due to the database updates in Home Assistant 2023.4 and thought I had this working, but a couple of the sensors are now showing an Unknown
value and my logs are full or errors:
Logger: homeassistant.components.sql.sensor
Source: components/sql/sensor.py:337
Integration: SQL (documentation, issues)
First occurred: 13:56:18 (1966 occurrences)
Last logged: 22:07:19
SELECT states.state FROM states WHERE state_id = ( SELECT states.old_state_id FROM states WHERE metadata_id = ( SELECT metadata_id FROM states_meta WHERE entity_id = 'input_boolean.person1_extended_away' ) AND old_state_id IS NOT NULL AND old_state_id NOT IN ('unknown', 'unavailable') AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) ORDER BY state_id DESC LIMIT 1 ); returned no results
SELECT states.state FROM states WHERE state_id = ( SELECT states.old_state_id FROM states WHERE metadata_id = ( SELECT metadata_id FROM states_meta WHERE entity_id = 'input_boolean.person2_extended_away' ) AND old_state_id IS NOT NULL AND old_state_id NOT IN ('unknown', 'unavailable') AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) ORDER BY state_id DESC LIMIT 1 ); returned no results
Here’s an example query, can anyone help?:
SELECT
states.state
FROM
states
WHERE
state_id = (
SELECT
states.old_state_id
FROM
states
WHERE
metadata_id = (
SELECT
metadata_id
FROM
states_meta
WHERE
entity_id = 'input_boolean.person1_extended_away'
)
AND old_state_id IS NOT NULL
AND old_state_id NOT IN ('unknown', 'unavailable')
AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY
old_state_id DESC
LIMIT
1
);