Extract complet Sensor values from Database

Configuration
1

Hi,
in Homeassistant I can see the sensor values from the whole year (till Feb 2023 when the Sensor was installed). However now i want to extract the raw values to perform some data analysis. But If I use following query in my mariadb I only get the vales from 2nd of September till today:

SELECT state,from_unixtime(last_updated_ts) FROM states 
WHERE metadata_id = 
   (  
   SELECT metadata_id  FROM states_meta 
   WHERE entity_id = 'sensor.strom_waschmaschine_voltage'
    ) 
ORDER BY last_updated_ts DESC;

| 230         | 2023-09-12 17:18:04.171999     |
| 231         | 2023-09-12 17:16:03.852040     |
[...]
| 237         | 2023-09-02 04:28:46.313127     |
| 236         | 2023-09-02 04:25:45.950273     |
| 237         | 2023-09-02 04:24:45.845622     |
+-------------+--------------------------------+
6833 rows in set (0,218 sec)

View in Homeassistant of the same Sensor:
Screenshot from 2023-09-12 17-17-45

Where can I find the rest of the values?

Best,
John

2

You are looking at the wrong table.

State values and events are only kept for 10 days by default.

Long term statistics are downsampled to hourly max, min and average data points and kept forever.

I don’t know the db structure but you probably want something like the “statistics” table.

3

Thanks!

SELECT mean,min,max,from_unixtime(start_ts) 
FROM statistics WHERE metadata_id =
 (  SELECT id     FROM       statistics_meta     
WHERE       statistic_id = 'sensor.strom_waschmaschine_voltage');

Did the trick!

2 Likes