SQL sensor “returned no results” 2023

Configuration
1

Hi, I made a similar post last year.

I have 2 sql sensors in my config and since my previous post they’ve been working fine.

They use the same query, I noticed recently that they are no longer working. My logs are full of warnings that the queries have “returned no results”.

This coincided with when I updated to Core 2023.2.

Logger: homeassistant.components.sql.sensor
Source: components/sql/sensor.py:240 
Integration: SQL (documentation, issues) 
First occurred: 20:34:05 (197 occurrences) 
Last logged: 21:22:38

SELECT * FROM states WHERE entity_id = "input_boolean.person1_extended_away" AND state != "unknown" AND last_updated < DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY state_id DESC LIMIT 1; returned no results
SELECT * FROM states WHERE entity_id = "input_boolean.person2_extended_away" AND state != "unknown" AND last_updated < DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY state_id DESC LIMIT 1; returned no results
2

last_updated Is now last_updated_ts.

Check the release notes topic. There are a few examples of how to update the query.

1 Like
3

Hi, thanks for pointing me in the right direction, to save someone else trawling, this comment was the most useful to me.

I’ve updated my query as follows, and this is now working:

SELECT state FROM states
WHERE entity_id = 'input_boolean.person1_extended_away'
AND state IS NOT NULL 
AND state NOT IN ('unknown', 'unavailable')
AND last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY state_id DESC LIMIT 1;