I was using MariaDB as database for my weather station software CumulusMX. To make my infrastructure more simple I decided to move also my HomeAssistant database from SQLite to MariaDB. In addition I
created own LongTermStatistics table on the same MariaDB instance.
I was already using SQL sensors and Appexchart for sensor state graphs. In similar way this could be used to show also long term statistics.
Setup is like following:
- Move HomeAssistant database from SQLite to MariaDB. A lot of guides are available on internet. I’m using DBeaver to manage MariaDB instance.
- With help of DBeaver I created a LongTermStatistics table on MariaDB, with columns like date, energy_used, temp_min, temp_max, etc. were to store daily sensor values.
- I added to HomeAssistant Node-RED new node to support connection to MariaDB - node-red-contrib-stackhero-mysql
- At 23:59 state of selected sensors is inserted into MariaDB - I use function node to generate SQL UPADATE statement for MariaDB node.
- To get LongTermStatistics I’m using the another function node to generate SQL SELECT statement which is passed then again to the MariaDB node. Results are stored as arguments in a specially created “Monthly data” sensor - I use Node-RED Companion to create this sensor.
There is a possibility to use also SQL sensors to extract data from MariaDB, but in this case I wasn’t able import data as sensor arguments, which is critical to use AppexCharts with data generator option. - I’m using AppexChart with data generator option to show “Monthly data sensor” arguments.
In this way I can visualize LongTermStatistics data on my HomeAssistant. - As far I’m using now own LongTermStatistics table on Maria DB, I can switch off standard HomeAssistant LongTermStatistics feature and reduce to Database size. To do so I added in configuration.yaml following lines
homeassistant:
customize_domain:
sensor:
state_class: none
May be this could setup be useful for others as well.