Hello,
I’ve started using data from two Shelly EM sensors to feed the Energy dashboard and I’ve observed that the database has growed a lot since then. I’ve made a SQL query to the database, and I see that power and voltage sensors have about 160000 states stored in the database, energy related sensor have about 8000 states. Usual sensor have about 4000-5000 states stored.
I store 14 days in the database.
To use the energy dashboard and store long term statistics is it necessary to store all those states? If not, how can I diminish database size without loosing energy dashboard data?
Thanks!
My understanding is that sensor states are used to create the “5 minute” statistics (in the statistics_short_term table), and these are then used to create the long term statistics (in the statistics table) used in the energy dashboard.
Assuming this is correct, technically you would only need to keep at least 5 minutes of sensor data to retain all of the long term statistics. However, purge_keep_days is the only current standard way of deleting sensor data, and it has no granularity over individual sensors - unless you want to run your own delete SQL as done here.
1 Like
Thank you.
I think I’ll avoid recording unnecessary entity states, like voltages, and let the others as is. Deleting by hand will be more or less useless as new data is generated coninuously.