{"id":74421,"date":"2020-03-17T08:43:08","date_gmt":"2020-03-17T05:43:08","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant"},"modified":"2020-03-17T08:43:08","modified_gmt":"2020-03-17T05:43:08","slug":"umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant","title":{"rendered":"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/f1133a4c391e901ed64bf0825a774fc6.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nEvery time I receive the bill for electricity and water, I wonder \u2014 does my family really consume that much? Sure, we have underfloor heating and a boiler in the bathroom, but they\u2019re 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 <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/411259\/\">connected water meters<\/a><\/noindex> and <noindex><a rel=\"nofollow\" href=\"https:\/\/www.youtube.com\/watch?v=XKvunh29sZo&amp;list=PLkt_cQxT0oUmfdxhbmTjytjaJjZjSVUOu\">electricity<\/a><\/noindex> to the smart home, but that was as far as it went. It\u2019s only now that I\u2019ve finally gotten around to analyzing the consumption, which is what this article is about.<\/p>\n<p>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.<\/p>\n<p>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.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>Task Definition<\/h2>\n<p>\nSo, the goal of today\u2019s exercise is to obtain beautiful graphs of water and electricity consumption:<\/p>\n<ul>\n<li>Hourly for 2 days<\/li>\n<li>Daily for 2 weeks<\/li>\n<li>(optionally) weekly and monthly<\/li>\n<\/ul>\n<p>\nThis presents us with some challenges:<\/p>\n<ul>\n<li>Standard graph components are usually quite basic. At best, you can create a line graph with points.\n<p>If you search well, you can find third-party components that enhance the capabilities of the standard graph. For Home Assistant, the component <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/kalkih\/mini-graph-card\">mini-graph-card<\/a><\/noindex>is quite nice and looks good, but it has its limitations:<\/p>\n<ul>\n<li>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)<\/li>\n<li>You can't add various entities to a single graph (for example, temperature and humidity, or combine a bar graph with a line)<\/li>\n<\/ul>\n<\/li>\n<li>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.\n<pre><code class=\"json\">{\"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}}}<\/code><\/pre>\n<p>\nI 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.<\/li>\n<li>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.\n<p>It\u2019s 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\u2019s 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\u2019t possible due to the architecture limitations of Home Assistant.<\/li>\n<\/ul>\n<p><\/p>\n<h2>Approach 1<\/h2>\n<p>\nFirst, let\u2019s 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\u2014utility_meter. <\/p>\n<p>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\u2014you just get the ready result. This component can be described in just a few lines in the configuration file.<\/p>\n<pre><code class=\"python\">utility_meter:\n  water_cold_hour_um:\n    source: sensor.water_meter_cold\n    cycle: hourly\n  water_cold_day_um:\n    source: sensor.water_meter_cold\n    cycle: daily\n<\/code><\/pre>\n<p>\nHere, sensor.water_meter_cold is the current value of the meter in liters that I receive <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/411259\/\">directly from the hardware<\/a><\/noindex> 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\u2019s a chart for the hourly accumulator over half a day.<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/d3c3ea29c10b17142e51ebe756ab7123.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nThe code for the hourly and daily graphs for the Lovelace UI looks like this:<\/p>\n<pre><code class=\"python\">      - type: history-graph\n        title: 'Hourly water consumption using vars'\n        hours_to_show: 48\n        entities:\n          - sensor.water_hour\n\n      - type: history-graph\n        title: 'Daily water consumption using vars'\n        hours_to_show: 360\n        entities:\n          - sensor.water_day\n<\/code><\/pre>\n<p>\nThe 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 \u2014 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.<\/p>\n<p>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.<\/p>\n<p>Here's the code for the card for lovelace-UI:<\/p>\n<pre><code class=\"python\">      - aggregate_func: max\n        entities:\n          - color: var(--primary-color)\n            entity: sensor.water_cold_hour_um\n        group_by: hour\n        hours_to_show: 48\n        name: \"Hourly water consumption aggregated by utility meter\"\n        points_per_hour: 1\n        show:\n          graph: bar\n        type: 'custom:mini-graph-card'<\/code><\/pre>\n<p>\nIn 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:<\/p>\n<ul>\n<li>group_by:hour \u2014 the graph will be generated with the columns aligned to the beginning of the hour<\/li>\n<li>points_per_hour: 1 \u2014 one column for each hour<\/li>\n<li>And most importantly, aggregate_func: max \u2014 to take the maximum value within each hour. This parameter transforms the sawtooth graph into columns.<\/li>\n<\/ul>\n<p>\n<img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/5bd866c39b23299e5f15dcf4a9bce38f.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nDon't pay attention to the row of columns on the left \u2014 this is standard behavior for the component when there is no data. And there wasn't any data \u2014 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). <\/p>\n<p>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 \u2014 the consumption there was normal, as can also be seen from the jagged graph.<\/p>\n<p>In general, I couldn't achieve credibility with this approach \u2014 the graph almost always shows something nonsensical.<\/p>\n<p>An analogous code for the daily sensor. <\/p>\n<pre><code class=\"python\">      - aggregate_func: max\n        entities:\n          - color: var(--primary-color)\n            entity: sensor.water_cold_day_um\n        group_by: interval\n        hours_to_show: 360\n        name: \"Daily water consumption aggregated by utility meter\"\n        points_per_hour: 0.0416666666\n        show:\n          graph: bar\n        type: 'custom:mini-graph-card'\n<\/code><\/pre>\n<p>\nNote 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 \u2014 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.<\/p>\n<h2>Approach 2<\/h2>\n<p>\nWhile still figuring out home assistant, I stumbled upon this video:<\/p>\n<p><center><div class=\"youtube-placeholder\" data-id=\"-0HrYFCRH0M\" onclick=\"loadVideo(this)\">\r\n        <img decoding=\"async\" src=\"https:\/\/img.youtube.com\/vi\/-0HrYFCRH0M\/hqdefault.jpg\" alt=\"Play video\" loading=\"lazy\" width=\"480\" height=\"360\" style=\"width:100%;height:auto;\">\r\n        <div class=\"play-button\"><\/div>\r\n    <\/div><\/center><br \/>\nA guy collects consumption data from various types of Xiaomi outlets. His task is a bit simpler \u2014 just to display the consumption for today, yesterday, and the month. No graphs are required. <\/p>\n<p>Let's set aside discussions about manually integrating instantaneous power readings \u2014 I\u2019ve 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. <\/p>\n<p>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.<\/p>\n<p>We'll create a variable starting_value_this_hour, where we will record the current meter readings.<br \/>\nAt 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 \u2014 we will save the value in the sensor, and in the future, we will build a graph based on this value. <br \/>\nWe also need to 'reset' the variable value_at_start_of_hour by writing the current value of the counter into it.<\/p>\n<p>All of this can be done through the functionalities of home assistant. <\/p>\n<p>We will need to write a bit more code than in the previous approach. First, we will create these 'variables'. We don\u2019t 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 \u2014 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.<\/p>\n<pre><code class=\"python\">- platform: mqtt\n  state_topic: \"test\/water\/hour\"\n  name: water_hour\n  unit_of_measurement: l\n\n- platform: mqtt\n  state_topic: \"test\/water\/hour_begin\"\n  name: water_hour_begin\n  unit_of_measurement: l\n\n- platform: mqtt\n  state_topic: \"test\/water\/day\"\n  name: water_day\n  unit_of_measurement: l\n\n- platform: mqtt\n  state_topic: \"test\/water\/day_begin\"\n  name: water_day_begin\n  unit_of_measurement: l<\/code><\/pre>\n<p>\nAll the magic happens in the automation, which runs every hour and every night, respectively.<\/p>\n<pre><code class=\"python\">- id: water_new_hour\n  alias: water_new_hour\n  initial_state: true\n  trigger:\n    - platform: time_pattern\n      minutes: 0\n  action:\n    - service: mqtt.publish\n      data:\n        topic: \"test\/water\/hour\"\n        payload_template: &gt;\n          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_hour_begin.state|int) }}\n        retain: true\n    - service: mqtt.publish\n      data:\n        topic: \"test\/water\/hour_begin\"\n        payload_template: &gt;\n          {{ states.sensor.water_meter_cold.state }}\n        retain: true\n\n- id: water_new_day\n  alias: water_new_day\n  initial_state: true\n  trigger:\n    - platform: time\n      at: \"00:00:00\"\n  action:\n    - service: mqtt.publish\n      data:\n        topic: \"test\/water\/day\"\n        payload_template: &gt;\n          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_day_begin.state|int) }}\n        retain: true\n    - service: mqtt.publish\n      data:\n        topic: \"test\/water\/day_begin\"\n        payload_template: &gt;\n          {{ states.sensor.water_meter_cold.state }}\n        retain: true<\/code><\/pre>\n<p>\nBoth automations perform 2 actions:<\/p>\n<ul>\n<li>They calculate the value over the interval as the difference between the initial and final values.<\/li>\n<li>They update the baseline value for the next interval.<\/li>\n<\/ul>\n<p>\nCreating graphs in this case is handled with a regular history-graph:<\/p>\n<pre><code class=\"python\">      - type: history-graph\n        title: 'Hourly water consumption using vars'\n        hours_to_show: 48\n        entities:\n          - sensor.water_hour\n\n      - type: history-graph\n        title: 'Daily water consumption using vars'\n        hours_to_show: 360\n        entities:\n          - sensor.water_day<\/code><\/pre>\n<p>\nIt looks like this:<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/ebde2fc5f0a72d7d6ba565c3686dc9c6.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nIn 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. <\/p>\n<p>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.<\/p>\n<h2>Approach 3<\/h2>\n<p>\nFortunately, 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.<\/p>\n<p>In addition to InfluxDB, smart people have also invented Grafana \u2014 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.<\/p>\n<p>To get inspired <noindex><a rel=\"nofollow\" href=\"https:\/\/dummylabs.com\/ru\/post\/2019-01-13-influxdb-part1\/\">here <\/a><\/noindex> and <noindex><a rel=\"nofollow\" href=\"https:\/\/dummylabs.com\/ru\/post\/2019-05-28-influxdb-part2\/\">here<\/a><\/noindex>. 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.<\/p>\n<p>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):<\/p>\n<pre><code class=\"python\">influxdb:\n  host: localhost\n  max_retries: 3\n  default_measurement: state\n  database: homeassistant\n  include:\n    entities:\n      - sensor.water_meter_hot\n      - sensor.water_meter_cold<\/code><\/pre>\n<p>\nWe will disable saving these same data to the internal Home Assistant database to avoid bloating it unnecessarily:<\/p>\n<pre><code class=\"python\">recorder:\n  purge_keep_days: 10\n  purge_interval: 1\n  exclude:\n    entities:\n      - sensor.water_meter_hot\n      - sensor.water_meter_cold<\/code><\/pre>\n<p>\nNow 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 \u2014 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.<\/p>\n<pre><code class=\"sql\">CREATE RETENTION POLICY \"month\" ON \"homeassistant\" DURATION 30d REPLICATION 1\nCREATE RETENTION POLICY \"year\" ON \"homeassistant\" DURATION 52w REPLICATION 1\nCREATE RETENTION POLICY \"infinite\" ON \"homeassistant\" DURATION INF REPLICATION 1<\/code><\/pre>\n<p>\nNow, the main trick \u2014 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).<\/p>\n<pre><code class=\"sql\">CREATE CONTINUOUS QUERY cq_water_hourly ON homeassistant \nBEGIN \n  SELECT max(value) AS value \n  INTO homeassistant.month.water_meter_hour \n  FROM homeassistant.autogen.l \n  GROUP BY time(1h), entity_id fill(previous) \nEND<\/code><\/pre>\n<p>\nThis command:<\/p>\n<ul>\n<li>Creates a continuous query named cq_water_cold_hourly in the homeassistant database.<\/li>\n<li>The query will execute every hour (time(1h)).<\/li>\n<li>The query will pull all data from the measurement homeassistant.autogen.l (liters), including readings of cold and hot water.<\/li>\n<li>The aggregated data will be grouped by entity_id, creating separate values for cold and hot water.<\/li>\n<li>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. <\/li>\n<li>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.<\/li>\n<\/ul>\n<p>\nAt 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. <\/p>\n<p>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 <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/influxdata\/influxdb\/issues\/3220\">has been discussed for several years now.<\/a><\/noindex>We'll address this issue later, but let fill(previous) in the continuous query be as it is\u2014it doesn't interfere. <\/p>\n<p>Let's check what we've got (of course, we have to wait a couple of hours):<\/p>\n<pre><code class=\"python\">&gt; select * from homeassistant.month.water_meter_hour group by entity_id\n...\nname: water_meter_hour\ntags: entity_id=water_meter_cold\ntime                 value\n----                 -----\n...\n2020-03-08T01:00:00Z 370511\n2020-03-08T02:00:00Z 370513\n2020-03-08T05:00:00Z 370527\n2020-03-08T06:00:00Z 370605\n2020-03-08T07:00:00Z 370635\n2020-03-08T08:00:00Z 370699\n2020-03-08T09:00:00Z 370761\n2020-03-08T10:00:00Z 370767\n2020-03-08T11:00:00Z 370810\n2020-03-08T12:00:00Z 370818\n2020-03-08T13:00:00Z 370827\n2020-03-08T14:00:00Z 370849\n2020-03-08T15:00:00Z 370921\n<\/code><\/pre>\n<p>\nPlease note that the values in the database are stored in UTC, so the times are off by 3 hours in this list\u2014the 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\u2014this is the peculiarity of the continuous query.<\/p>\n<p>As you can see, the aggregated value is also a monotonically increasing sequence, although the records are less frequent\u2014once an hour. But that's not a problem\u2014we can write another query that will pull the correct data for the chart.<\/p>\n<pre><code class=\"sql\">SELECT difference(max(value)) \nFROM homeassistant.month.water_meter_hour \nWHERE entity_id='water_meter_cold' and time &gt;= now() -24h \nGROUP BY time(1h), entity_id \nfill(previous)<\/code><\/pre>\n<p>\nLet me explain:<\/p>\n<ul>\n<li>We will extract data from the homeassistant.month.water_meter_hour for entity_id='water_meter_cold' from the last day (time &gt;= now() -24h). <\/li>\n<li>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).<\/li>\n<li>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.<\/li>\n<\/ul>\n<p>\nThe result looks like this<\/p>\n<pre><code class=\"python\">name: water_meter_hour\ntags: entity_id=water_meter_cold\ntime                 difference\n----                 ----------\n...\n2020-03-08T02:00:00Z 2\n2020-03-08T03:00:00Z 0\n2020-03-08T04:00:00Z 0\n2020-03-08T05:00:00Z 14\n2020-03-08T06:00:00Z 78\n2020-03-08T07:00:00Z 30\n2020-03-08T08:00:00Z 64\n2020-03-08T09:00:00Z 62\n2020-03-08T10:00:00Z 6\n2020-03-08T11:00:00Z 43\n2020-03-08T12:00:00Z 8\n2020-03-08T13:00:00Z 9\n2020-03-08T14:00:00Z 22\n2020-03-08T15:00:00Z 72<\/code><\/pre>\n<p>\nFrom 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.<\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/c6f551590cd141438b644b24aa4ddebf.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nI will display data for cold and hot water on one graph. The query is exactly the same as I described above. <\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/cb47fc8570b7fd1305ce3451e0518f11.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nTo add the resulting graph to Home Assistant, you need to:<\/p>\n<ul>\n<li>exit the editing mode of the graph. For some reason, the correct sharing settings for graphs are only suggested from the dashboard page.<\/li>\n<li>Click on the triangle next to the graph name, and select share from the menu.<\/li>\n<li>In the opened window, go to the embed tab.<\/li>\n<li>Uncheck the current time range box \u2014 the time range will be set via the URL. <\/li>\n<li>Select the desired theme. In my case, it is light.<\/li>\n<li>Copy the resulting URL into the Lovelace-UI settings card.<\/li>\n<\/ul>\n<p><\/p>\n<pre><code class=\"python\">      - type: iframe\n        id: graf_water_hourly\n        url: \"http:\/\/192.168.10.200:3000\/d-solo\/rZARemQWk\/water?orgId=1&amp;panelId=2&amp;from=now-2d&amp;to=now&amp;theme=light\"\n<\/code><\/pre>\n<p>\nNote that the time range (the last 2 days) is set here, not in the dashboard settings. <\/p>\n<p>The graph looks like this. I did not use hot water in the last 2 days, so only the cold water graph is drawn.<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/d18c82712d3b829a97ada8c8e2adce24.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nI 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:<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/557297e666f9cd73c3d2aa2fd80c0a2d.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nThis is what this graph looks like:<\/p>\n<p><img decoding=\"async\" alt=\"Smart Home: Building Usage Graphs for Water and Electricity in Home Assistant\" src=\"\/wp-content\/uploads\/2020\/03\/ac8c53569c6b05aeb22e05969de4cd6d.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nSo, 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. <\/p>\n<p>All shown graphs are dynamic. You can hover the mouse over an interesting point to see details and the value at that specific point. <\/p>\n<p>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. <\/p>\n<p>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. <\/p>\n<p>I see at least two solutions:<\/p>\n<ul>\n<li>Ignore monthly graphs and stick to weekly ones. 52 weekly bars a year look quite good.<\/li>\n<li>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 \u2014 Grafana can do that too.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>\nI 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.<\/p>\n<p>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 <noindex><a rel=\"nofollow\" href=\"https:\/\/dummylabs.com\/ru\/post\/2019-01-13-influxdb-part1\">the aforementioned article<\/a><\/noindex> \u2014 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.<\/p>\n<p>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\u2014the graphs will be created in the same way as for water.<\/p>\n<p>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.<br \/>\n<br \/>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/492314\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041a\u0430\u0436\u0434\u044b\u0439 \u0440\u0430\u0437 \u043f\u043e\u043b\u0443\u0447\u0430\u044f \u043f\u043b\u0430\u0442\u0435\u0436\u043a\u0443 \u0437\u0430 \u044d\u043b\u0435\u043a\u0442\u0440\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0438 \u0432\u043e\u0434\u0443 \u044f \u0443\u0434\u0438\u0432\u043b\u044f\u044e\u0441\u044c \u2014 \u043d\u0435\u0443\u0436\u0435\u043b\u0438 \u043c\u043e\u044f \u0441\u0435\u043c\u044c\u044f \u0441\u0442\u043e\u043e\u043e\u043e\u043e\u043b\u044c\u043a\u043e \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u0435\u0442? \u041d\u0443 \u0434\u0430, \u0432 \u0432\u0430\u043d\u043d\u043e\u0439 \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d \u0442\u0435\u043f\u043b\u044b\u0439 \u043f\u043e\u043b \u0438 \u0431\u043e\u0439\u043b\u0435\u0440, \u043d\u043e \u0432\u0435\u0434\u044c \u043e\u043d\u0438 \u0436\u0435 \u043d\u0435 \u043a\u043e\u0447\u0435\u0433\u0430\u0440\u044f\u0442 \u043f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u043e. \u0412\u043e\u0434\u0443 \u0442\u043e\u0436\u0435 \u0432\u0440\u043e\u0434\u0435 \u044d\u043a\u043e\u043d\u043e\u043c\u0438\u043c (\u0445\u043e\u0442\u044f \u043f\u043e\u043f\u043b\u0435\u0441\u043a\u0430\u0442\u044c\u0441\u044f \u0432 \u0432\u0430\u043d\u043d\u043e\u0439 \u0442\u043e\u0436\u0435 \u043b\u044e\u0431\u0438\u043c). \u041d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043b\u0435\u0442 \u043d\u0430\u0437\u0430\u0434 \u044f \u0443\u0436\u0435 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u043b \u0441\u0447\u0435\u0442\u0447\u0438\u043a\u0438 \u0432\u043e\u0434\u044b \u0438 \u044d\u043b\u0435\u043a\u0442\u0440\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u043a \u0443\u043c\u043d\u043e\u043c\u0443 \u0434\u043e\u043c\u0443, [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":74422,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-74421","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041a\u0430\u0436\u0434\u044b\u0439 \u0440\u0430\u0437.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0423\u043c\u043d\u044b\u0439 \u0434\u043e\u043c: \u0421\u0442\u0440\u043e\u0438\u043c \u0433\u0440\u0430\u0444\u0438\u043a\u0438 \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u0435\u043d\u0438\u044f \u0432\u043e\u0434\u044b \u0438 \u044d\u043b\u0435\u043a\u0442\u0440\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u0432 Home Assistant | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041a\u0430\u0436\u0434\u044b\u0439 \u0440\u0430\u0437.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-03-17T05:43:08+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-03-17T05:43:08+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Smart Home: Building graphs for water and electricity consumption in Home Assistant | ProHoster","description":"Every time.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0423\u043c\u043d\u044b\u0439 \u0434\u043e\u043c: \u0421\u0442\u0440\u043e\u0438\u043c \u0433\u0440\u0430\u0444\u0438\u043a\u0438 \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u0435\u043d\u0438\u044f \u0432\u043e\u0434\u044b \u0438 \u044d\u043b\u0435\u043a\u0442\u0440\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u0432 Home Assistant | ProHoster","og:description":"\u041a\u0430\u0436\u0434\u044b\u0439 \u0440\u0430\u0437.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/umnyj-dom-stroim-grafiki-potrebleniya-vody-i-elektrichestva-v-home-assistant","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-17T05:43:08+00:00","article:modified_time":"2020-03-17T05:43:08+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"74421","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:15:28","updated":"2022-09-27 16:07:45","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/74421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=74421"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/74421\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/74422"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=74421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=74421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=74421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}