I have a temp sensor and want to get its values directly from the database.
I was trying different approaches but I cannot get it right:
if I try this sql
select last_changed, last_updated, state from states
where
entity_id = 'sensor.sonoffth2_temperature'
AND state not in ('unknown', 'unavailable', '')
order by last_updated desc
limit 20;
I’m getting duplicated entries:
last_changed | last_updated | state
-------------------------------+-------------------------------+-------
2022-06-20 20:29:16.927033+02 | 2022-06-20 20:38:42.725464+02 | 25.69
| 2022-06-20 20:29:16.927033+02 | 25.69
2022-06-20 19:59:16.31399+02 | 2022-06-20 20:29:16.926143+02 | 25.68
2022-06-20 19:59:16.31399+02 | 2022-06-20 20:27:51.45263+02 | 25.68
2022-06-20 19:59:16.31399+02 | 2022-06-20 20:21:55.005479+02 | 25.68
2022-06-20 19:59:16.31399+02 | 2022-06-20 20:20:58.804227+02 | 25.68
| 2022-06-20 19:59:16.31399+02 | 25.68
2022-06-20 19:29:15.712794+02 | 2022-06-20 19:59:16.313438+02 | 25.75
2022-06-20 19:29:15.712794+02 | 2022-06-20 19:40:52.013879+02 | 25.75
| 2022-06-20 19:29:15.712794+02 | 25.75
2022-06-20 18:59:15.129137+02 | 2022-06-20 19:29:15.709731+02 | 25.84
2022-06-20 18:59:15.129137+02 | 2022-06-20 19:21:53.768343+02 | 25.84
i ended up with this sql:
select last_updated, state from states
where
entity_id = 'sensor.sonoffth2_temperature'
AND (last_changed is null OR last_changed=last_updated)
AND state not in ('unknown', 'unavailable', '')
order by last_updated desc
limit 20;
but I get still some duplicates.
Does anyone have a clue what sql should I use?
Rgds,
Konrad