How to query database to get unique values from a sensor?

Configuration
1

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

2

I’m not seeing duplicates in your results above that you posted for the first query.

What level of detail are you looking for? Just the data tied to last_updated, but maybe just date and time without taking it past the minutes?

3

Konrad, (if you’re still bothered after 3 years!) try adding

AND last_changed is null

By the way, we’ve now got some good examples here: https://www.home-assistant.io/integrations/sql/