Every time I receive a payment for electricity and water, I wonder - does my family really consume sooooo much? Well, yes, there is a heated floor and a boiler in the bathroom, but they don’t work as firemen all the time. We also seem to save water (although we also like splashing in the bathroom). A few years ago I already и to a smart home, but this is where things got stuck. The hands have reached the analysis of consumption only now, which, in fact, is what this article is about.
I recently switched to Home Assistant as my smart home system. One of the reasons was just the ability to organize the collection of a large amount of data with the possibility of convenient construction of various kinds of graphs.
The information described in this article is not new, all these things under different sauces have already been described on the Internet. But each article, as a rule, describes only one approach or aspect. I had to compare all these approaches and choose the most suitable one myself. The article still does not provide exhaustive information on data collection, but is a kind of summary of how I did it. So constructive criticism and suggestions for improvement are welcome.
Formulation of the problem
So, the goal of today's exercise is to get beautiful graphs of water and electricity consumption:
- Hourly for 2 days
- Daily for 2 weeks
- (optional) weekly and monthly
There are some difficulties in this:
- The standard chart components tend to be quite poor. At best, you can build a line graph by points.
If you search well, you can find third-party components that extend the capabilities of the standard chart. For home assistant, in principle, a good and beautiful component , but it is also somewhat limited:
- It is difficult to set the parameters of the bar chart at large intervals (the width of the bar is set in fractions of an hour, which means that intervals longer than an hour will be set in fractional numbers)
- You cannot add different entities to one graph (for example, temperature and humidity, or combine a bar graph with a line)
- Not only does the home assistant use the most primitive SQLite database by default (and I, the handyman, did not master the installation of MySQL or Postgres), the data is not stored in the most optimal way. So, for example, with each change of each even the smallest digital parameter of a parameter, a huge json about a kilobyte in size is written to the database
{"entity_id": "sensor.water_cold_hourly", "old_state": {"entity_id": "sensor.water_cold_hourly", "state": "3", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:05:06.897604+00:00", "last_updated": "2020-02-23T19:05:06.897604+00:00", "context": {"id": "aafc8ca305ba4e49ad4c97f0eddd8893", "parent_id": null, "user_id": null}}, "new_state": {"entity_id": "sensor.water_cold_hourly", "state": "4", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:11:11.251545+00:00", "last_updated": "2020-02-23T19:11:11.251545+00:00", "context": {"id": "0de64b8af6f14bb9a419dcf3b200ef56", "parent_id": null, "user_id": null}}}I have quite a few sensors (temperature sensors in every room, water and electricity meters), and some also generate quite a lot of data. For example, only the SDM220 electricity meter generates about a dozen values every 10-15 seconds, and I would like to install 8 such meters. And there is also a whole bunch of parameters that are calculated based on other sensors. That. all these values can easily inflate the database by 100-200 MB daily. In a week, the system will barely toss and turn, and in a month the flash drive will die (in the case of a typical installation of home assistant on raspberry PI), and there can be no talk of storing data for a whole year.
- If you are lucky, your meter itself can count consumption. You can contact the meter at any time and ask what time the accumulated consumption value is. As a rule, all electricity meters that have a digital interface (RS232/RS485/Modbus/Zigbee) provide such an opportunity.
Worse, if the device can simply measure some instantaneous parameter (for example, instantaneous power or current), or simply generate pulses every X watt-hours or liters. Then you need to think about how and with what to integrate it and where to accumulate value. There is a risk of missing the next report for any reason, and the accuracy of the system as a whole raises questions. You can, of course, entrust all this to a smart home system like home assistant, but no one has canceled the point about the number of entries in the database, and polling sensors more than once a second will not work (a limitation of the home assistant architecture).
Approach 1
First, let's see what home assistant is provided out of the box. Measuring consumption over a period is a highly requested functionality. Of course, it was implemented a long time ago as a specialized component - utility_meter.
The essence of the component is that it starts the current_accumulated_value variable inside and resets it after a specified period (hour/week/month). The component itself monitors the incoming variable (the value of some kind of sensor), subscribes to changes in the value itself - you just get the finished result. This thing is described in just a few lines in the configuration file
utility_meter:
water_cold_hour_um:
source: sensor.water_meter_cold
cycle: hourly
water_cold_day_um:
source: sensor.water_meter_cold
cycle: daily
Here sensor.water_meter_cold is the current value of the meter in liters that I get by mqtt. The design creates 2 new sensors water_cold_hour_um and water_cold_day_um, which accumulate hourly and daily readings, resetting them to zero after a period. Here is a graph of the hourly battery for half a day.
The hourly and daily chart code for lovelace-UI looks like this:
- type: history-graph
title: 'Hourly water consumption using vars'
hours_to_show: 48
entities:
- sensor.water_hour
- type: history-graph
title: 'Daily water consumption using vars'
hours_to_show: 360
entities:
- sensor.water_day
Actually, in this algorithm lies the problem of this approach. As I already mentioned, for each incoming value (the current meter reading for each next liter), 1kb of a record is generated in the database. Each utility meter also generates a new value, which is also added to the base. If I want to collect hourly/daily/weekly/monthly readings, yes, for several water risers, and even add a pack of electric meters, this will be a lot of data. Well, more precisely, there is not much data, but since the home assistant writes a bunch of unnecessary information to the database, the size of the database will grow by leaps and bounds. I'm even afraid to estimate the size of the base for weekly and monthly charts.
In addition, the utility meter itself does not solve the problem. The utility meter plot is a monotonically increasing function that resets to 0 every hour. We also need a user-friendly consumption schedule, how many liters were eaten during the period. The standard history-graph component does not do this, but the external mini-graph-card component can help us.
This is the card code for lovelace-UI:
- aggregate_func: max
entities:
- color: var(--primary-color)
entity: sensor.water_cold_hour_um
group_by: hour
hours_to_show: 48
name: "Hourly water consumption aggregated by utility meter"
points_per_hour: 1
show:
graph: bar
type: 'custom:mini-graph-card'In addition to the standard settings like the sensor name, graph type, color (I didn’t like the standard orange), it’s important to note 3 settings here:
- group_by:hour - the chart will be generated with columns aligned to the beginning of the hour
- points_per_hour: 1 - one bar per hour
- And most importantly, aggregate_func: max is to take the maximum value within each hour. It is this parameter that turns the sawtooth chart into bars.
Do not pay attention to the row of columns on the left - this is the standard behavior of the component if there is no data. But there was no data - I only turned on data collection using the utility meter a couple of hours ago just for the sake of this article (I will describe my current approach a little lower).
In this picture, I wanted to show that sometimes the data display even works, and the bars really reflect the correct values. But that's not all. For some reason, the highlighted column for the period from 11 am to 12 am displays 19 liters, although on the toothy graph a little higher for the same period from the same sensor we see consumption of 62 liters. Either a bug or hands are crooked. But I still don’t understand why the data on the right broke off - the consumption there was normal, which is also visible from the toothy graph.
In general, I failed to achieve the plausibility of this approach - the graph almost always shows some kind of heresy.
Similar code for the daytime sensor.
- aggregate_func: max
entities:
- color: var(--primary-color)
entity: sensor.water_cold_day_um
group_by: interval
hours_to_show: 360
name: "Daily water consumption aggregated by utility meter"
points_per_hour: 0.0416666666
show:
graph: bar
type: 'custom:mini-graph-card'
Please note that the group_by parameter is set to interval, and the points_per_hour parameter rules everything. And this is another problem with this component - points_per_hour works well on charts of an hour or less, but disgustingly on larger intervals. So to get one column in one day, I had to enter the value 1/24=0.04166666. I'm not talking about weekly and monthly charts.
Approach 2
While still figuring out the home assistant, I came across this video:
The comrade collects consumption data from several types of Xiaomi sockets. His task is a little simpler - just display the value of consumption for today, yesterday and for the month. No charts required.
Let's leave aside the arguments about the manual integration of instantaneous power values - I already wrote about the “accuracy” of this approach above. It is not clear why he did not use the accumulated consumption values, which are already collected by the same outlet. In my opinion, integration inside the piece of iron will work better.
From the video, we will take the idea of manually counting consumption for a period. For a man, only the values for today and for yesterday are considered, but we will go further and try to draw a graph. The essence of the proposed method in my case is as follows.
We will create a variable value_at_the_beginning_of_hour, in which we will write the current counter readings
According to the timer at the end of the hour (or at the beginning of the next), we calculate the difference between the current reading and the one stored at the beginning of the hour. This difference will be the consumption for the current hour - we will save the value to the sensor, and in the future we will build a graph based on this value.
You also need to “reset” the variable value_at_beginning_of_hour by writing the current value of the counter there.
All this can be done through well ... by means of the home assistant itself.
You will have to write a little more code than in the previous approach. Let's start with these "variables". Out of the box, we do not have the “variable” entity, but you can use the services of an mqtt broker. We will send values there with the retain=true flag - this will save the value inside the broker, and it can be pulled out at any time, even when the home assistant is rebooted. I made hourly and daily counters at once.
- platform: mqtt
state_topic: "test/water/hour"
name: water_hour
unit_of_measurement: l
- platform: mqtt
state_topic: "test/water/hour_begin"
name: water_hour_begin
unit_of_measurement: l
- platform: mqtt
state_topic: "test/water/day"
name: water_day
unit_of_measurement: l
- platform: mqtt
state_topic: "test/water/day_begin"
name: water_day_begin
unit_of_measurement: lAll the magic happens in the automation, which runs every hour and every night, respectively.
- id: water_new_hour
alias: water_new_hour
initial_state: true
trigger:
- platform: time_pattern
minutes: 0
action:
- service: mqtt.publish
data:
topic: "test/water/hour"
payload_template: >
{{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_hour_begin.state|int) }}
retain: true
- service: mqtt.publish
data:
topic: "test/water/hour_begin"
payload_template: >
{{ states.sensor.water_meter_cold.state }}
retain: true
- id: water_new_day
alias: water_new_day
initial_state: true
trigger:
- platform: time
at: "00:00:00"
action:
- service: mqtt.publish
data:
topic: "test/water/day"
payload_template: >
{{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_day_begin.state|int) }}
retain: true
- service: mqtt.publish
data:
topic: "test/water/day_begin"
payload_template: >
{{ states.sensor.water_meter_cold.state }}
retain: trueBoth automations do 2 things:
- Calculate the value per interval as the difference between the start and end value
- Update the base value for the next interval
The construction of graphs in this case is solved by the usual history-graph:
- type: history-graph
title: 'Hourly water consumption using vars'
hours_to_show: 48
entities:
- sensor.water_hour
- type: history-graph
title: 'Daily water consumption using vars'
hours_to_show: 360
entities:
- sensor.water_dayIt looks like this:
In principle, this is already what you need. The advantage of this method is that the data is generated once per interval. Those. a total of 24 entries per day for the hourly chart.
Unfortunately, this still does not solve the general problem of a growing base. If I want a monthly consumption graph, I will have to store data for at least a year. And since home assistant provides only one storage duration setting for the entire database, this means that ALL data in the system will have to be stored for a whole year. For example, in a year I consume 200 cubic meters of water, which means 200000 entries in the database. And if you take into account other sensors, then the figure becomes generally indecent.
Approach 3
Fortunately, smart people have already solved this problem by writing the InfluxDB database. This database is specially optimized for storing time-based data and is ideal for storing values of different sensors. The system also provides a SQL-like query language that allows you to extract values from the database and then aggregate them in various ways. Finally, different data can be stored for different times. For example, frequently changing readings such as temperature or humidity can be stored for only a couple of weeks, while daily readings of water consumption can be stored for a whole year.
In addition to InfluxDB, smart people also invented Grafana, a system for drawing graphs from data from InfluxDB. Grafana can draw different types of charts, customize them in detail, and, most importantly, these charts can be “plugged” into the lovelace-UI home assistant.
be inspired и . The articles describe in detail the process of installing and connecting InfluxDB and Grafana to home assistant. I will focus on solving my specific problem.
So, first of all, let's start adding the counter value in influxDB. A piece of the home assistant configuration (in this example, I will have fun not only with cold, but also with hot water):
influxdb:
host: localhost
max_retries: 3
default_measurement: state
database: homeassistant
include:
entities:
- sensor.water_meter_hot
- sensor.water_meter_coldLet's disable the saving of the same data in the home assistant internal database, so as not to inflate it once again:
recorder:
purge_keep_days: 10
purge_interval: 1
exclude:
entities:
- sensor.water_meter_hot
- sensor.water_meter_coldLet's now go to the InfluxDB console and set up our database. In particular, you need to configure how long certain data will be stored. This is regulated by the so-called. retention policy - this is similar to databases inside the main database, with each internal database having its own settings. By default, all data is added to the retention policy called autogen, this data will be stored for a week. I would like hourly data to be stored for a month, weekly data for a year, and monthly data to never be deleted at all. We will create appropriate retention policies
CREATE RETENTION POLICY "month" ON "homeassistant" DURATION 30d REPLICATION 1
CREATE RETENTION POLICY "year" ON "homeassistant" DURATION 52w REPLICATION 1
CREATE RETENTION POLICY "infinite" ON "homeassistant" DURATION INF REPLICATION 1Now, in fact, the main trick is data aggregation using continuous query. This is a mechanism that automatically launches a query at specified intervals, aggregates the data for this query, and adds the result to a new value. Let's look at an example (I write in a column for readability, but in reality I had to enter this command on one line)
CREATE CONTINUOUS QUERY cq_water_hourly ON homeassistant
BEGIN
SELECT max(value) AS value
INTO homeassistant.month.water_meter_hour
FROM homeassistant.autogen.l
GROUP BY time(1h), entity_id fill(previous)
ENDThis command:
- Creates a continuous query named cq_water_cold_hourly in the homeassistant database
- The query will be executed every hour (time(1h))
- The query will pull out all data from measurement'a homeassistant.autogen.l (liters), including readings of cold and hot water
- Aggregated data will be grouped by entity_id, which will create separate values for cold and hot water.
- Since the counter of liters is a monotonically increasing sequence within each hour, you will need to take the maximum value, so the aggregation will be carried out by the max(value) function
- The new value will be written to homeassistant.month.water_meter_hour where month is the name of the retention policy with a retention period of one month. Moreover, data on cold and hot water will be scattered into separate records with the corresponding entity_id and the value in the value field
At night or when no one is at home, there is no water consumption, and accordingly there are no new records in homeassistant.autogen.l either. To avoid missing values in normal queries, you can use fill(previous). This will force InfluxDB to use the past hour value.
Unfortunately, continuous query has a peculiarity: the fill(previous) trick doesn't work and records are simply not created. Moreover, this is some kind of insurmountable problem, which . We will deal with this problem later, and let fill(previous) in continuous query be there - it does not interfere.
Let's check what happened (of course, you need to wait a couple of hours):
> select * from homeassistant.month.water_meter_hour group by entity_id
...
name: water_meter_hour
tags: entity_id=water_meter_cold
time value
---- -----
...
2020-03-08T01:00:00Z 370511
2020-03-08T02:00:00Z 370513
2020-03-08T05:00:00Z 370527
2020-03-08T06:00:00Z 370605
2020-03-08T07:00:00Z 370635
2020-03-08T08:00:00Z 370699
2020-03-08T09:00:00Z 370761
2020-03-08T10:00:00Z 370767
2020-03-08T11:00:00Z 370810
2020-03-08T12:00:00Z 370818
2020-03-08T13:00:00Z 370827
2020-03-08T14:00:00Z 370849
2020-03-08T15:00:00Z 370921
Note that the values in the database are stored in UTC, so this list differs by 3 hours - the 7am values in the InfluxDB output match the 10am values in the charts above. Also note that between 2 and 5 in the morning there are simply no records - this is the very feature of continuous query.
As you can see, the aggregated value is also a monotonically increasing sequence, only the entries are less frequent - once an hour. But this is not a problem - we can write another query that will extract the correct data for the chart.
SELECT difference(max(value))
FROM homeassistant.month.water_meter_hour
WHERE entity_id='water_meter_cold' and time >= now() -24h
GROUP BY time(1h), entity_id
fill(previous)I will decrypt:
- From the homeassistant.month.water_meter_hour database, we will pull data for entity_id='water_meter_cold' for the last day (time >= now() -24h).
- As I mentioned, some entries may be missing from the homeassistant.month.water_meter_hour sequence. We will regenerate this data by running the query with GROUP BY time(1h). This time, fill(previous) will work properly, generating the missing data (the function will take the previous value)
- The most important thing in this query is the difference function, which will calculate the difference between the hour marks. By itself, it does not work and requires an aggregation function. Let this be the max() used before.
The execution result looks like this
name: water_meter_hour
tags: entity_id=water_meter_cold
time difference
---- ----------
...
2020-03-08T02:00:00Z 2
2020-03-08T03:00:00Z 0
2020-03-08T04:00:00Z 0
2020-03-08T05:00:00Z 14
2020-03-08T06:00:00Z 78
2020-03-08T07:00:00Z 30
2020-03-08T08:00:00Z 64
2020-03-08T09:00:00Z 62
2020-03-08T10:00:00Z 6
2020-03-08T11:00:00Z 43
2020-03-08T12:00:00Z 8
2020-03-08T13:00:00Z 9
2020-03-08T14:00:00Z 22
2020-03-08T15:00:00Z 72From 2 am to 5 am (UTC) there was no consumption. Nevertheless, the query will return the same consumption value thanks to fill(previous), and the difference function will subtract this value from itself and get 0 at the output, which is actually required.
The only thing left to do is to build a graph. To do this, open Grafana, open some existing (or create a new) dashboard, create a new panel. The chart settings will be as follows.
I will display cold and hot water data on the same graph. The request is exactly the same as I described above.
Display parameters are set as follows. For me it will be a graph with lines (lines), which goes in steps (stairs). The Stack parameter will be explained below. There are a couple more display options below, but they are not so interesting.
To add the resulting graph to home assistant, you need to:
- exit the chart editing mode. For some reason, the correct chart sharing settings are offered only from the dashboard page
- Click on the triangle next to the chart name, select share from the menu
- In the window that opens, go to the embed tab
- Uncheck current time range - we will set the time range via URL
- Select the required topic. In my case it is light
- Copy the resulting URL to the lovelace-UI settings card
- type: iframe
id: graf_water_hourly
url: "http://192.168.10.200:3000/d-solo/rZARemQWk/water?orgId=1&panelId=2&from=now-2d&to=now&theme=light"
Please note that the time range (last 2 days) is set here, and not in the dashboard settings.
The chart looks like this. I have not used hot water in the last 2 days, so only a graph of cold water is drawn.
I haven’t decided for myself which chart I like best, a step line, or real bars. Therefore, I will simply give an example of a daily consumption schedule, only this time in bars. Queries are built in the same way as described above. The display options are:
This chart looks like this:
So, about the Stack parameter. In this graph, a cold water bar is drawn on top of a hot bar. The total height corresponds to the total consumption of cold and hot water for the period.
All graphs shown are dynamic. You can move the mouse over the point of interest and see the details and value at a particular point.
Unfortunately, it was not without a couple of fly in the ointment. On a bar chart (unlike the graph with step lines), the middle of the bar is not in the middle of the day, but at 00:00. Those. the left half of the bar is drawn in place of the previous day. So the charts for Saturday and Sunday are drawn a little to the left of the bluish zone. Until I figured out how to win it.
Another problem is the inability to work correctly with monthly intervals. The fact is that the length of the hour / day / week is fixed, but the length of the month is different every time. InfluxDB can only work with equal intervals. So far, my brains have been enough to set a fixed interval of 30 days. Yes, the chart will float a little during the year and the bars will not exactly correspond to the months. But since this thing is interesting to me just as a display meter, I'm ok with this.
I see at least two solutions:
- To score on monthly charts and limit yourself to weekly ones. 52 weekly bars in a year look pretty good
- Consider the monthly consumption itself as method No. 2, and use the grafana only for beautiful graphs. It's a pretty accurate solution. You can even overlay charts for the past year for comparison - grafana can do that.
Conclusion
I don't know why, but I love these kinds of charts. They show that life is in full swing and everything is changing. Yesterday there was a lot, today there is little, tomorrow there will be something else. It remains to work with households on the topic of consumption. But even with current appetites, just a large and incomprehensible figure in the bill is already turning into a fairly understandable picture of consumption.
Despite my almost 20-year career as a programmer, I practically did not intersect with databases. Therefore, installing an external database seemed like something so abstruse and incomprehensible. Everything has changed - it turned out that screwing a suitable tool is done in a couple of clicks, and with a specialized tool, the task of plotting becomes a little easier.
In the title, I mentioned electricity consumption. Unfortunately, at the moment I can not provide any graph. One SDM120 meter is dead, and the other is buggy when accessed via Modbus. However, this does not affect the topic of this article in any way - the graphs will be built in the same way as for water.
In this article, I have given those approaches that I have tried myself. Surely there are some other ways to organize the collection and visualization of data that I do not know about. Tell me about it in the comments, I will be very interested. I will be glad to constructive criticism and new ideas. I hope the above material will also help someone.
Source: habr.com