Get historic instance of sensor data

[alexbeatle]

Hello, folks.
Is there a way to get a historic instance of sensor data?

My immediate need:
Create a button, when pressed will pop up calendar where I can choose a date\time and it will grab odometer reading from my car. This will generate a set of reminders to check on tires’ wear and advise when to rotate/change my tires.
If I press the button right after the tires’ service, it’s easy - grab current odometer reading. But in the event I forget to do it on the day of tire service, I want to have an option to do it later, grabbing the odometer reading from the sensor historic data.
Thanks in advance.

[Biscuit (Geoff)]

Home Assistant keeps everything for 10 days in the states table. It also keeps statistics on measurement and metered entity states at 5 minute intervals, then after 10 days it purges this to keep an hourly summary as long-term statistics. This is kept forever.

Using SQL you can indeed read the statistics table. You can either use the HA SQL integration to create a sensor and perform an SQL query directly on the statistics table (as long as you only return one value) or you can perform the SQL read in Node-RED (to return an array and for further processing).

NOTE: It is advisable to ONLY perform reads and not modify the HA data tables in any way !

Assuming that you are using the HA standard SQLite, it helps to add the SQLite Web add-on as you will need to look in the statistics_meta table, which is a mapping between entities and the id used in the statistics table. If you can’t find the entity you want in here, then it is not being stored in the short & long-term statistics (but your graph above shows that it is).

Once you have the statistics id of the entity you want, move to the statistics table and perform an SQL query to extract the data you required.

To use this in Node-RED, install the node-red-node-sqlite node into the palette.

Set the node for prepared statements with configuration for read only, and for the standard HA database, and add the SQL statement. You will need to set msg.params to {} if not using parameters, but by sending in the date you want, setting the SQL to return only records after 22:30, it will get just the last record of the day at 23:00.

And this should return the state value of the entity of your choice at the end of the day of your choice.

I am sure that the button and popup calendar will be the easy part by comparison.

[alexbeatle]

Thank you!
Is there a way to keep retrying different timestamps if no data is returned? For example in the case like this…

[Biscuit (Geoff)]

You could loop, incrementing the search date, until something come back, but there are probably better ways to either improve the SQL or pull more data than you want and process the search in Node-RED.

The main entity state table holds every state change event, so if an entity goes ‘unavailable’ then that will appear as the state value at that time. There are no time gaps in the state value, hence we see ‘unavailable / unknown’ “holes” in the entity-state history.

The statistics tables, both sort and long-term, take a snapshot of the statistics of the entity state value over five minutes and over one hour. The statistics ‘entity state’ value in the long-term statistics is (if I understand this correctly) the average or sum of the entity state over the full hour, and this will therefore capture a value where there was at least one real state value during the hour period. In your graph example above, there will be a long-term ‘state’ value for 6:00, 7:00 and 8:00 as the gap does not span a full one hour period. The statistics entry will only not exist if the base entity state was unavailable for the entire hour.

In most cases you can just use SQL to request the exact entry at, say, “2024-10-15” and “18:00” and expect to get something. It is not difficult to expand this to cover the full day, so all of “2024-10-15” and then to order and group by time, so that you can pick the last entry of the day. This will deal with, for example, where the sensor went off-line at 12:00 and there is no 18:00, but there is a record for 11:00.

If you don’t want to do all the work in the SQL statement, then it is very easy to pull, for example, an entire year of data. Since the long-term stats table holds only 24 records per day, that is an array of around 9000 items for a year, and it is not difficult to then use Node-RED and either JS or JSONata to process the array there.

If I wanted to do your example, I would execute an SQL statement to pull an entire year (or even the entire data set) grouped by day, so I get one end-of-day odometer figure per day, then use JSONata to knock this into shape. From there I would take the date of interest and return either the exact match from the date-odometer array, or find the first entry in the date following.