Hi
I’m struggling to find ways of diagnosing my issue so posting on this forum…
I’d like to get the entity_id of the last sensor changed using the following yaml set in my sensors.yaml:
- platform: sql
db_url: postgresql://loginname:somepassword@postgreSQLServerIP/hass
queries:
- name: SQLTest
query: "select 1.0 as mysd;"
column: 'mysd'
- name: SQLLastSensor
query: "select entity_id,state,last_changed from states order by state_id desc"
column: 'entity_id'
- name: Gym Bathroom Humidity Standard Deviation
query: "select TRUNC(stddev(sd.svalue),2) as mysd from (SELECT CAST(s.state AS NUMERIC) as svalue FROM states s WHERE s.entity_id = 'sensor.shower_humidity' AND s.state<>'unavailable' AND s.state<>'unknown' ORDER BY state_id DESC LIMIT 60) sd;"
column: 'mysd'
I have confirmed the logonname, somepassword and postgreSQLServerIP via pgAdmin 4 and run the queries directly in the Query tool, all return a single line result.
select 1.0 as mysd;
returns mysd as numeric 1.0
select entity_id,state,last_changed from states order by state_id desc limit 1;
returns 3 columns
select TRUNC(stddev(sd.svalue),2) as mysd from (SELECT CAST(s.state AS NUMERIC) as svalue FROM states s WHERE s.entity_id = 'sensor.shower_humidity' AND s.state<>'unavailable' AND s.state<>'unknown' ORDER BY state_id DESC LIMIT 60) sd;
returns mysd as numeric 2.13
When I restart HA and inspect the values of sensor.sqltest I correctly get a State of 1.0.
When I check the State of the remaining SQL sensors the values returned are ‘unknown’
I looked at home-assistant.log and only the following sql entries were there:
2021-05-12 17:19:41 INFO (SyncWorker_3) [homeassistant.loader] Loaded sql from homeassistant.components.sql
2021-05-12 17:19:43 INFO (MainThread) [homeassistant.components.sensor] Setting up sensor.sql
2021-05-12 17:19:54 WARNING (MainThread) [homeassistant.components.sensor] Setup of sensor platform sql is taking over 10 seconds.
Is there anywhere else I can see what’s going on?
PS originally I wanted to limit the number of return rows to 1 so I had originally added limit 1;
at the end of the queries, but this threw an error in the logs showing there was an additional LIMIT 1 added to the line.
Any thoughts of what else I should try?