Using Maria DB for Long Term Statistics

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:

  1. Move HomeAssistant database from SQLite to MariaDB. A lot of guides are available on internet. I’m using DBeaver to manage MariaDB instance.
  2. 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.
  3. I added to HomeAssistant Node-RED new node to support connection to MariaDB - node-red-contrib-stackhero-mysql
  4. At 23:59 state of selected sensors is inserted into MariaDB - I use function node to generate SQL UPADATE statement for MariaDB node.
  5. 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.
  6. 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.
  7. 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
    state_class: none

May be this could setup be useful for others as well.