Hello
I’m looking for a way to store data from home assistant entities/sensors for longer than the standard 10 days, and to make graphs with it.
what do you use for this? what is most used? is that with influxdb and grafana for example? or timescaledb?.. thanks in advance
Ps i use docker (compose) so if someone has easy to follow instructions for it please let me know.
I would consider to switch the recorder to an MariaDB - which could run on a different machine, for example.
With having enough disk space, it could collect data for nearly a year… depends on the amount of sensors and things like that.
Also, sure - the logbook or history will become slower, when you select a huge time range to display… but at all, it should work.
my last setup (before doing a fresh installation) was nearly 15 GB database … with… (i am not sure - 3 Months or half a year of history)…
anyway - such a configuration - and to prevent a massive amount of used disk space, I would really recommend to check, what you need to have in your recorders - and exclude not required information 
Doublechecking… statistics on devices are covering a much longer period, could you maybe provide a use case (also for my learning) why you want the individual registration longer than (say) 10 days?
I would like to compare data over several years, then you will find out trends, for example your electricity and gas consumption, what has been the average per year and whether you have used more and have to do something about it.
I also find it interesting to know, for example, how many times our smart doorbell has gone off, not very useful information, but collecting this kind of data just gives you a lot of insight
The one I use is Influx but as a rel.db. stuck guy, I am not too fond of it as I prefer to play around.
MariaDB is my main recorder but have not yet understood how this could also be used aside (!) the main recorder…not-yet as far from my prio 
storing data for several years will be an issue due to the fact, that Homeassistant is writing each state change to the Database.
For getting trends and really “longterm” data, I think other methods should be considered - outside from what HomeAssistant can do.
I was planning such a thing, too - but haven’t yet started with this…
One Idea that came up in a discussion was, to create another database (beside the one HomeAssistant is using) - and use NodeRed to store specific data in it.
The idea was somehow like:
INSERT INTO mytable(sensorname, lastresetdate, consumption) VALUES
(:friendly_name, :last_reset, :solaredge_monthly_kwh_consumption) ON
DUPLICATE KEY UPDATE consumption = :solaredge_monthly_kwh_consumption;
This should write the sensors state into the database - and it should create a new line, when the sensor will be resetted.
But as of now, the SQL hasn’t been tested and it is just a theoretical “how it could work”
IN that case, wouldn’t it be easier to have your main recorder MariaDb and run statements to the secondary db directly?
Maria DB will be my main database for recorder.
But the HA recorder will record each value change - and - I don’t need to store too much data in the recorder-db.
The benefit of the secondary database, is, that I don’t need to create queries on the tables created from HomeAssistant.
I could just use the entities I want in nodeRed - write the data I want into the Database - and since that will be my own db layout, I could do “whatever I want” with this data.
It will at least store less data than homeassistant will do…
I could create NodeRed Dashboars, or could automatically create CSV export information on the information I want to get… without needing to know the full db layout from homeassistant - I don’t have to deal with the entitie-IDs in the database, or whatever.
In my point of view, there’s a huge maintenance benefit of writing the data that I want into a second maria db… ( I could even just delete the data without loosing data inside of HA)
How did you setup that u can use both ?
I have installed mariadb on a other windows system.
I want also to still use mariadb and the HA database.
I haven’t.
There’s only one Recorder possible in HA.
An Idea could be, to use the internal sqlite DB - and then use nodeRed to Collect the Data you want to Store in a Maria DB and Use a MySQL Extension in node Red.
But then, you can’t Access this Data Grimm HomeAssistant itself.
But tbh - I don’t see the reason for having two recorders
Well, the problem is if i want to keep the temperature sensor data for years… i don’t know how to do that.
These are really needed now.
I want these to be access by homeassistant itself.
So is there a way to let the xiaomi zigbee sensors act like a long term sensor so it won’t get purged?
regards
you can use a maria db as recorder - and increase the purge-intervall.
You could also create a template sensor that is using the xiaomi sensor values - and use the state_class as measurement for example.
That SHOULD also activate the long term statistics.
But I don’t understand, why you want to use two recorders (mysql and ha internal sqlite)
I explain.
Right now i have the data of the hass database, whats over 2 years, i dont want to lose it, and there is no safe way to migrate that database into mariaDB
if i have a second recorder like mariaDB i could let it save the data on an external server.
so i could split the devices for example the long-term statistics without creating tons of templates for temperature/humidity devices to keep them in my db.
So than i could use both to read the data in HASS.
if there is a safe easy way to migrate the data into MariaDB ill do that.
But i checked a lot of topics here, included the Migrate topic and its to complicated and still you can lose a lot of data during the migration.
I could set the Keep_days option to 1000 or something but it will fill up my space on the hass server.
and there is no way to let hass save the db on an external drive
that’s true.
There isn’t really an easy and safe way to migrate the data between two databases.
the only thing I COULD imagine - but without any warranty and untested by myself:
but as I said, no warranty at all - and I am not even sure if HA can handle this “switch”
ok step 1.2.3 i already done.
How can i import it into mariadb ?
i have mariaDB installed on a windows machine
regards
you should probably consider to use a software such as PHPMyAdmin for administrative jobs on your MariaDB - but it should also work per command line…
Sadly just exporting to sql results an error importing into mariaDB
i need to find an other way to go from home-assistant_v2.db to sql
what error did you get?
I found a way to do it and it works really good and easy, just tested out