Home Assistant is great at running a state-model of entities from various integrations, and these can be used to easily create dashboards and automations based on state values and change events. What HA is not designed to do is store ‘user-data’ ad hoc. This means that reporting, summarizing or analysing the (long-term) historic entity-state is not a simple task.
I run HA for monitoring of my Solar PV system, and also pull in my energy use and billing consumption data from my UK utility company. I use historical utility billing data in several different ways, including composing historic billing information.
There are a couple of ways to approach getting at historical data. Since HA does not keep user-data, other than permitting input_entities which are auto-restored after restarts, any historical data older than the default 10-day window has to either come from the long-term statistics, or has to be stored independently to HA.
Long-term statistics
These are hourly samples of entity state values, saved together with appropriate statistical values (sum, average, min, max etc) and this data lasts forever. The downside is that not all entities are saved, no attribute data is saved, and you need to perform SQL enquiries on the long-term stats table directly so as to get data back. It does have the advantage of not requiring any other data storage.
HA has an SQL integration, which adds an entity based on an SQL query. As long as you can write the necessary query to return just one value, it works. Here I am computing the total (electrical) energy cost for the past week, taken from long-term data.
It is just a case of adding the SQL (sensor) integration and adding the SQL required.
SELECT SUM(state) AS total FROM
(SELECT id, state, udate FROM
(SELECT metadata_id AS id, state, date(created_ts, "unixepoch") AS udate, time(created_ts, "unixepoch") AS utime
FROM "statistics"
WHERE metadata_id = 115 AND unixepoch()-created_ts < 86400 * 7 AND utime > "20" AND utime < "23"
ORDER BY udate, utime DESC)
GROUP BY udate)
This is pulled from the HA statistics table, which uses ‘metadata_id’ as a reference for the entity, and this entity to metadata_id mapping can easily be read from the statistics_meta table by inspection.
The SQLite Web add-on makes access to the standard HA database very easy (just don’t edit the tables or contents!)
If you are happy to experiment, you may well find that you can create a new SQL entity value for each field (historic value) you want, each being taken from the long-term history of the relevant ‘bill estimate’ sensor you already have.
Own database
Since HA is not intended for user data storage, you have to go off piste for this one, although it is actually easier to do than should be admitted to.
Using the main HA database by adding additional tables is clearly risky, so I went down the route of purchasing a second HA machine (can’t break my main machine), setting up MariaDB (can’t break SQLite), and using Node-RED to collate a set of entity values each hour, which is pulled from the main machine to the second machine, and then stored in the DB every 24 hours. I now have 580+ days worth of hourly records for Solar PV, battery, grid and load energy use, together with battery SOC, solar PV forecast, weather, electricity energy tariff (agile) costs and utility energy consumption. This still requires SQL to extract and build the information I want, but with Node-RED I can generate tables of data and use it as I wish.
MariaDB comes as an HA add-on and can be run additionally and entirely separately to the standard SQLite setup. The phpMyAdmin add-on works like SQLite Web but for MariaDB, so I created and maintain the database tables manually using that.
Node-RED, although not as popular as templating, is easy enough to use with the mysql node set. Since MariaDB is server based rather than direct file access (SQLite) off-machine access is also possible.
I hope this helps!