I’d like to start saving some data from my sensors for further explorations with grafana or similar. All the solutions I’ve found always require that I store all my data on a database, but I’m not looking for that. I’d like to keep the home assistant SQLite database as is with the 7 day data and save some personalized information Like daily max and min temp, max and min wind, monthly KWh consumptions, etc. on a database.
Something similar with File but to write on a database would work. Any idea on how could I achieve that?
In the past I have exported data to the file notification service for later trending. Exporting to CSV format is rather easy. Some other formats may be tricky.
I use postgresql as the database for HA and wrote two simple triggers to copy each inserted record in states and events into archive copies of each of these databases. These fire each time HA inserts a record into its databases. I mirror the structures of states and events in these databases but remove all of the unneeded indexes and constraints from the archive copies.
These allows me to have a core database for HA with less records, this seems to keep the performance of database actions better from within HA, while allowing me to have unlimited history.
I am not an SQLite expert but there does appear to be the ability to write triggers in this database as well that would do similar copy operations. I do not know the performance overhead that would occur in SQLite. In my postgresql configuration, the overhead seems very small when these triggers execute. I have over 150 million records in the archive databases and doing long analytic queries on these has very little impact on database operations on the HA database from within HA.
I ran Grafana for awhile but I found it was easier for me to use standard sql from within jupyter docker image for my analytic work. Less for me to learn and the knowledge base for jupyter and sql is larger. Here is a link for jupyter provided docker images and howtos:
What do you mean with specific data? You can choose which entities should be pushed to the InfluxDB. Then afterwards you can adjust your SQL Query accordingly to show the data you want.
So all the entities I do not push will still keep using the SQLite database? It is possible to also push data that is not from a sensor, for eg the min and max temp from a sensor from the day?
The SQLite database from Home Assistant is configured independently from InfluxDB. See the docs for the influxDB integration for details about how to blacklist/whitelist entities that should be pushed to InfluxDB. The entities that will be in Home Assistant’s SQLite DB are configured through the recorder integration.
You could create a statistics sensor for it and then include this sensor in the config for InfluxDB or push the whole days data to InfluxDB and let InfluxDB calculate the min/max values for you. You shouldn’t need to care about DB size so much with InfluxDB, just try it.
