Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant

Every time I receive the bill for electricity and water, I wonder — does my family really consume that much? Sure, we have underfloor heating and a boiler in the bathroom, but they’re not running constantly. We also seem to save water (though we do enjoy a good soak in the tub). A few years ago, I already connected water meters and electricity to the smart home, but that was as far as it went. It’s only now that I’ve finally gotten around to analyzing the consumption, which is what this article is about.

Recently, I switched to Home Assistant as my smart home system. One of the reasons was the ability to collect a lot of data and easily build various types of graphs.

The information described in this article isn't new; all these things have been written about on the Internet in various forms. But each article usually covers only one approach or aspect. I had to compare all these approaches and choose the most suitable one. This article doesn't provide exhaustive information on data collection, but it serves as a sort of summary of how I did it. Constructive criticism and suggestions for improvement are welcome.

Task Definition

So, the goal of today’s exercise is to obtain beautiful graphs of water and electricity consumption:

• Hourly for 2 days
• Daily for 2 weeks
• (optionally) weekly and monthly

This presents us with some challenges:

• Standard graph components are usually quite basic. At best, you can create a line graph with points.

  If you search well, you can find third-party components that enhance the capabilities of the standard graph. For Home Assistant, the component mini-graph-cardis quite nice and looks good, but it has its limitations:

  • It's difficult to set the parameters for bar graphs over large intervals (the width of the bar is set in fractions of an hour, which means intervals longer than an hour will be set using decimal numbers)
  • You can't add various entities to a single graph (for example, temperature and humidity, or combine a bar graph with a line)
• Not only does the home assistant use the most basic SQLite database by default (and I, being technically challenged, couldn't manage to install MySQL or Postgres), but the data is also stored in a suboptimal way. For example, each time any tiny digital parameter changes, a massive JSON of about a kilobyte 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 each room, water and electricity meters), and some of them generate a significant amount of data. For instance, the SDM220 electricity meter generates about a dozen readings every 10-15 seconds, and I would like to install around 8 of such meters. Plus, there is a whole set of parameters that are calculated based on other sensors. Thus, all these values can easily inflate the database by 100-200 MB daily. After a week, the system will barely function, and after a month, the flash drive will fail (in the case of a typical home assistant installation on a Raspberry Pi), not to mention storing data for an entire year.

• If you're lucky, your meter can count consumption by itself. You can check the accumulated consumption value from the meter at any time. Generally, all electricity meters with a digital interface (RS232/RS485/Modbus/Zigbee) provide such capability.

  It’s worse if the device can only measure a specific instantaneous parameter (like instantaneous power or current), or just generate pulses every X watt-hours or liters. Then you need to think about how and with what to integrate this and where to store the value. There’s a risk of missing another report for some reason, and the accuracy of the system as a whole raises questions. Of course, all of this could be entrusted to a smart home system like Home Assistant, but the point about the number of records in the database still stands, and polling sensors more frequently than once a second isn’t possible due to the architecture limitations of Home Assistant.

Approach 1

First, let’s look at what Home Assistant provides out of the box. Measuring consumption over a period is quite in demand. Naturally, this has long been implemented as a specialized component—utility_meter.

The essence of the component is that it internally creates a variable called current_accumulated_value, resetting it after the specified period (hour/week/month). The component itself monitors the incoming variable (the value from some sensor), and subscribes to changes in the value—you just get the ready result. This component can be 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 receive directly from the hardware via MQTT. The structure creates two new sensors, water_cold_hour_um and water_cold_day_um, which accumulate hourly and daily readings, resetting them after the period ends. Here’s a chart for the hourly accumulator over half a day.

The code for the hourly and daily graphs for the 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

The problem with this approach lies in the algorithm itself. As I mentioned before, for each input value (the current reading of the meter for each subsequent liter), 1 KB of data is generated in the database. Each utility meter also generates new values, which are added to the database. If I want to collect hourly/daily/weekly/monthly readings, and include several water stacks, plus a bunch of electric meters — this will involve a vast amount of data. Well, technically, the data itself isn't too abundant, but since the home assistant writes a lot of unnecessary information to the database, its size will grow exponentially. I'm even afraid to estimate the size of the database for weekly and monthly graphs.

Besides, the utility meter by itself does not solve the stated task. The graph of values produced by the utility meter is a monotonically increasing function that resets to 0 every hour. What we need is a user-friendly graph of consumption, showing how many liters have been consumed over a period. The standard history-graph component can't do this, but we can use an external component called mini-graph-card.

Here's the code for the card 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, and color (I wasn't fond of the default orange), it's important to note 3 settings:

• group_by:hour — the graph will be generated with the columns aligned to the beginning of the hour
• points_per_hour: 1 — one column for each hour
• And most importantly, aggregate_func: max — to take the maximum value within each hour. This parameter transforms the sawtooth graph into columns.

Don't pay attention to the row of columns on the left — this is standard behavior for the component when there is no data. And there wasn't any data — I only started collecting data through the utility meter a couple of hours ago just for this article (I'll explain my current approach below).

In this picture, I wanted to show that sometimes data visualization actually works, and the bars accurately reflect the correct values. However, this is not the case for all data. The highlighted bar between 11 AM and 12 PM shows 19 liters for some reason, whereas the jagged graph slightly above indicates a consumption of 62 liters for the same period from the same sensor. Either it's a bug, or there's a user error. I'm also not sure why the data on the right got cut off — the consumption there was normal, as can also be seen from the jagged graph.

In general, I couldn't achieve credibility with this approach — the graph almost always shows something nonsensical.

An analogous code for the daily 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'

Note that the group_by parameter is set to interval, and the points_per_hour parameter is what controls everything. This brings another problem with this component — points_per_hour works well on graphs for an hour or less but performs poorly on larger intervals. To get a single bar for one day, I had to enter the value 1/24=0.04166666. Not to mention weekly and monthly graphs.

Approach 2

While still figuring out home assistant, I stumbled upon this video:

A guy collects consumption data from various types of Xiaomi outlets. His task is a bit simpler — just to display the consumption for today, yesterday, and the month. No graphs are required.

Let's set aside discussions about manually integrating instantaneous power readings — I’ve already mentioned the 'accuracy' of such an approach above. It's unclear why he didn't use the accumulated consumption values already being gathered by the same outlet. In my opinion, integrating within the device will work better.

From the video, we'll take the idea of manually calculating consumption over a period. The guy counts only the values for today and yesterday, but we will go further and try to draw a graph. The essence of the proposed method in my case is as follows.

We'll create a variable starting_value_this_hour, where we will record the current meter readings.
At the end of the hour (or at the beginning of the next), we will calculate the difference between the current reading and the one recorded at the start of the hour. This difference will be the consumption for the current hour — we will save the value in the sensor, and in the future, we will build a graph based on this value.
We also need to 'reset' the variable value_at_start_of_hour by writing the current value of the counter into it.

All of this can be done using the built-in capabilities of Home Assistant.

We will need to write a bit more code than in the previous approach. First, we will create these 'variables'. We don’t have an entity called 'variable' out of the box, but we can use the services of an MQTT broker. We will send values there with the flag retain=true — this will save the values inside the broker, and they can be retrieved at any time, even after restarting Home Assistant. I made both hourly and daily counters right away.

- 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: l

All 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: true

Both automations perform 2 actions:

• They calculate the value over the interval as the difference between the initial and final values.
• They update the baseline value for the next interval.

Creating graphs in this case is handled with a regular 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_day

It looks like this:

In principle, this is already what is needed. The advantage of this method is that the data is generated once per interval. That is, only 24 records per day for the hourly graph.

Unfortunately, this still does not solve the overarching issue of the growing database. If I want a monthly consumption graph, I will have to store data for at least a year. And since Home Assistant only provides a single storage duration setting for the entire database, this means that ALL data in the system will have to be kept for an entire year. For example, I consume 200 cubic meters of water in a year, which means 200,000 entries in the database. And if I also consider other sensors, the number becomes quite excessive.

Approach 3

Fortunately, smart people have already addressed this issue by creating the InfluxDB database. This database is specially optimized for storing time-based data and is ideal for keeping values from various sensors. The system also provides an SQL-like query language that allows extracting values from the database and aggregating them in various ways. Finally, different data can be stored for different lengths of time. For example, frequently changing readings like temperature or humidity can be kept for just a couple of weeks, while daily water consumption readings can be kept for an entire year.

In addition to InfluxDB, smart people have also invented Grafana — a graphing system for data from InfluxDB. Grafana can create various types of graphs, customize them in detail, and, most importantly, these graphs can be 'embedded' in the Lovelace UI of Home Assistant.

To get inspired here and here. The articles provide a detailed description of the installation and connection process for InfluxDB and Grafana to Home Assistant. I will focus on solving my specific task.

So, first, let's start by adding the counter value to InfluxDB. Here is a snippet of the Home Assistant configuration (in this example, I will play with both cold and hot water):

influxdb:
  host: localhost
  max_retries: 3
  default_measurement: state
  database: homeassistant
  include:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

We will disable saving these same data to the internal Home Assistant database to avoid bloating it unnecessarily:

recorder:
  purge_keep_days: 10
  purge_interval: 1
  exclude:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

Now let's move to the InfluxDB console and set up our database. In particular, we need to configure how long certain data will be stored. This is governed by the so-called retention policy — which is similar to having databases within the main database, with each internal database having its own settings. By default, all data is stored in a retention policy called autogen, which will keep this data for a week. I would like hourly data to be kept for a month, weekly data for a year, and monthly data to never be deleted. Let's create the corresponding 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 1

Now, the main trick — aggregating data using a continuous query. This is a mechanism that automatically executes a query at specified intervals, aggregates the data based on that query, and stores the result as a new value. Let's break it down with an example (I will write in a column for readability, but in reality, I had to input this command in 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) 
END

This command:

• Creates a continuous query named cq_water_cold_hourly in the homeassistant database.
• The query will execute every hour (time(1h)).
• The query will pull all data from the measurement homeassistant.autogen.l (liters), including readings of cold and hot water.
• The aggregated data will be grouped by entity_id, creating separate values for cold and hot water.
• Since the liter counter is a monotonically increasing sequence, within each hour, we will need to take the maximum value, so the aggregation will be done using the max(value) function.
• The new value will be recorded in homeassistant.month.water_meter_hour, where month is the name of the retention policy with a storage duration of one month. Furthermore, the data for hot and cold water will be stored in separate records with their corresponding entity_id and value field.

At night or when no one is home, there is no water consumption, and accordingly, there are no new records in homeassistant.autogen.l. To avoid missing value entries in regular queries, you can use fill(previous). This will instruct InfluxDB to use the previous hour's value.

Unfortunately, there is a peculiarity with the continuous query: the fill(previous) trick does not work, and records are simply not created. Moreover, this is some kind of insurmountable problem that has been discussed for several years now.We'll address this issue later, but let fill(previous) in the continuous query be as it is—it doesn't interfere.

Let's check what we've got (of course, we have 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

Please note that the values in the database are stored in UTC, so the times are off by 3 hours in this list—the values for 7 AM in the InfluxDB output correspond to the values for 10 AM on the graphs above. Also, note that there are simply no records between 2 AM and 5 AM—this is the peculiarity of the continuous query.

As you can see, the aggregated value is also a monotonically increasing sequence, although the records are less frequent—once an hour. But that's not a problem—we can write another query that will pull 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)

Let me explain:

• From the homeassistant.month.water_meter_hour database, we will extract data for entity_id=’water_meter_cold’ for the last 24 hours (time >= now() -24h).
• As I already mentioned, there may be some missing records in 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 as needed, generating the missing data (the function will take the previous value).
• The most important part of this query is the difference function, which will calculate the difference between the hourly timestamps. It does not work by itself and requires an aggregate function. Let's use max() as it was used before.

The 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 72

From 2 to 5 AM (UTC), there was no consumption. However, the query will return the same consumption value thanks to fill(previous), and the difference function will subtract this value from itself, resulting in 0, which is exactly what is needed.

The only thing left is to build the graph. To do this, we will open Grafana, either open an existing dashboard (or create a new one), and create a new panel. The graph settings will be as follows.

I will display data for cold and hot water on one graph. The query is exactly the same as I described above.

The display parameters are set as follows. I will use a line graph (lines) that follows a step pattern (stairs). I will explain the Stack parameter a bit later. There are a couple of other display parameters below, but they are not as interesting.

To add the resulting graph to Home Assistant, you need to:

• exit the editing mode of the graph. For some reason, the correct sharing settings for graphs are only suggested from the dashboard page.
• Click on the triangle next to the graph name, and select share from the menu.
• In the opened window, go to the embed tab.
• Uncheck the current time range box — the time range will be set via the URL.
• Select the desired theme. In my case, it is light.
• Copy the resulting URL into 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"

Note that the time range (the last 2 days) is set here, not in the dashboard settings.

The graph looks like this. I did not use hot water in the last 2 days, so only the cold water graph is drawn.

I still haven't decided which graph I like more, the step line or the actual bars. Therefore, I will just provide an example of a daily consumption graph, but this time with bars. The queries are constructed similarly to the above description. The display parameters are as follows:

This is what this graph looks like:

So, about the Stack parameter. In this graph, the cold water bar is drawn on top of the hot water bar. The total height corresponds to the total consumption of cold and hot water over the period.

All shown graphs are dynamic. You can hover the mouse over an interesting point to see details and the value at that specific point.

Unfortunately, there is a bit of negativity. In a bar chart (unlike a step-line chart), the center of the bar is not in the middle of the day, but at 00:00. That is, the left half of the bar is drawn in the place of the previous day. So, the graphs for Saturday and Sunday are slightly to the left of the bluish zone. I haven't figured out how to overcome this yet.

Another issue is the inability to work correctly with monthly intervals. The thing is, the length of an hour/day/week is fixed, but the length of a month varies each time. InfluxDB can only work with uniform intervals. So far, I've managed to set a fixed interval of 30 days. Yes, the chart will drift a bit over the year, and the bars won't correspond precisely to the months. But since I'm only interested in this for metric demonstration purposes, I'm fine with it.

I see at least two solutions:

• Ignore monthly graphs and stick to weekly ones. 52 weekly bars a year look quite good.
• Calculate monthly consumption as method #2 and use Grafana just for pretty graphs. It will turn out to be quite accurate. You can even overlay graphs from last year for comparison — Grafana can do that too.

Conclusion

I don't know why, but I love this kind of graphs. They show that life is buzzing and everything is changing. Yesterday there was a lot, today there is little, tomorrow will be something else. I just need to work with my household about consumption. But even with current appetites, just a large and unclear number on the bill turns into a quite understandable picture of consumption.

Despite nearly 20 years as a programmer, I had hardly dealt with databases. Therefore, installing an external database seemed like something complex and obscure. Everything changed with the aforementioned article — it turned out that integrating the right tool is done in just a few clicks, and with the specialized tool, the task of building graphs becomes a bit easier.

In the title, I've mentioned electricity consumption. Unfortunately, at the moment, I can't provide any graphs. One SDM120 meter has died, and the other malfunctions when accessed via Modbus. However, this does not impact the topic of this article—the graphs will be created in the same way as for water.

In this article, I outlined the approaches I have personally tried. There are surely other methods for organizing data collection and visualization that I am not aware of. Please let me know about them in the comments; I would be very interested. I welcome constructive criticism and new ideas. I hope the material presented will also help someone.

Source: habr.com