Smart Meter: Power consumption visualization with Shelly, InfluxDB, flux language, Grafana

Tutorials & Examples Solutions
1

Hi,

Goal

Starting point

  • Once configured, shelly devices do report their measured power consumption (Watt) every 5ish seconds via MQTT.
    For example:
00:00h - 10 Watt
00:05h - 60 Watt
00:10h -  5 Watt

Pre-requisites (won’t be described)

  • openHAB 3.x
  • Shelly device (e.g. Shelly3EM or Shelly1PM from Allterco Robotics)
  • openHAB Shelly Binding
  • MQTT broker
  • InfluxDB 2
  • Shelly and openHAB configured to use MQTT broker
  • openHAB persistence using InfluxDB 2
  • Grafana 9.x connected to InfluxDB 2

Setup

item

  • A NUMBER item will be used to save the values provided by Shelly (to the Influx DB via persistence)
Number:Power Shelly3EM_ALL_CURRENTWATTS "Power [%d %unit%]" { channel="shelly:shellyem3:c45bbe6a849a:device#accumulatedWatts" }

logging (optional)

  • As the openHAB event log will be flooded with value updates, I have disabled logging for all item names that contain the string CURRENTWATTS
  • I added the following line to my /var/lib/openhab/etc/log4j2.xml
<RegexFilter regex=".*(CURRENTWATTS).*" onMatch="DENY" onMismatch="NEUTRAL"/>
  • The whole important part looks like this:
<!-- Event log appender -->
<RollingRandomAccessFile fileName="${sys:openhab.logdir}/events.log" filePattern="${sys:openhab.logdir}/events.log.%i.gz" name="EVENT">
        <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%-5.5p] [%-36.36c] - %m%n"/>
        <RegexFilter regex=".*(CURRENTWATTS).*" onMatch="DENY" onMismatch="NEUTRAL"/>
        <Policies>
                <OnStartupTriggeringPolicy/>
                <SizeBasedTriggeringPolicy size="16 MB"/>
        </Policies>
        <DefaultRolloverStrategy max="7"/>
</RollingRandomAccessFile>

Grafana / flux language

from(bucket: "openHAB")
	  |> range(start: -1y, stop: now())
	  |> filter(fn: (r) => r["_measurement"] == "Shelly3EM_ALL_CURRENTWATTS")
	  |> aggregateWindow(fn: mean, every: 1d)
	  |> map(fn: (r) => ({ r with _value: float(v: r._value) * 24.0 / 1000.0}))
	  |> aggregateWindow(fn: sum, every: 1mo)
		|> timeShift(duration:-1mo)

The code above belongs to a Grafana TimeSeries graph. I’ll try to explain each line separately.

  • from(bucket: "openHAB")
  • Grafana will read from Influx DB 2 bucket openHAB
  • |> range(start: -1y, stop: now())
  • The final graph will show values for each of the last 12 months (1 year in the past, until now).
  • |> filter(fn: (r) => r["_measurement"] == "Shelly3EM_ALL_CURRENTWATTS")
  • Values will be read from _measurement column for item Shelly3EM_ALL_CURRENTWATTS
  • |> aggregateWindow(fn: mean, every: 1d)
  • First calculation creates a mean value for each day.
    For example: If you constantly consumed 100 Watt for 12 hours and then you turn off the device (0 Watt measured) for the next 12 hours, the mean value will be 50 Watt for the whole day.
  • |> map(fn: (r) => ({ r with _value: float(v: r._value) * 24.0 / 1000.0}))
    Rewriting values or rather converting Watt to kWh.
    For example: 50 Watt consumption per day will be converted to 1.2kWh
    |> aggregateWindow(fn: sum, every: 1mo)
    As I would like to see the consumption per month instead of per day, let’s sum up the daily values per month. Btw: It’s great that Grafana provided this function as it would be a headache to manage all the different months with different amount of days.
    |> timeShift(duration:-1mo)
    Without this line the final Grafana chart would show your calculated consumption for January with a final timestamp as 1st of Feb! So we have to shift the time one month back.

image
image1497×934 81 KB

7 Likes
2

Hi ,

could you help me with influx query ?
I store the totals:

        Type number : l0t "L0" [ stateTopic="shellies/shelly-wp/emeter/0/total"]
        Type number : l1t "L1" [ stateTopic="shellies/shelly-wp/emeter/1/total"]
        Type number : l2t "L2" [ stateTopic="shellies/shelly-wp/emeter/2/total"]

in my influxdb of the shelly em3

So now I would like to have same as you on monthly based how many energy I have consumed.

I tried for one phase but this is total nonsense :slight_smile:

from(bucket: "openhab")
	  |> range(start: -1y, stop: now())
	  |> filter(fn: (r) => r["_measurement"] == "LWP_L0T")
	  |> aggregateWindow(fn: mean, every: 1d)
	  |> map(fn: (r) => ({ r with _value: float(v: r._value)  / 1000.0}))
	  |> aggregateWindow(fn: sum, every: 1mo)
		|> timeShift(duration:-1mo)
3

I understand you save the total consumption, which means your value keeps increasing every time. Unfortunately I don’t know the solution for this scenario but I assume you need to calculate the difference between value at the end of the month and value at the beginning of the month.

4

I’m working on a similar requirement.

I’ve a shelly 2pm that is connect to my PV and which collects the power produced.

Item:

Number solarGarageWatts     "Solarzellen Leistung [%.1f W]"        <solarplant>  (gPower)       { channel="shelly:shelly25-relay:XXXXX1:meter1#currentWatts"}

This value is persisted in influxdb:

0	2022-12-20T12:59:09.685159894Z	2023-01-19T12:59:09.685159894Z	2022-12-24T09:00:00Z	8.326201298701296	value	solarGarageWatts	solarGarageWatts
0	2022-12-20T12:59:09.685159894Z	2023-01-19T12:59:09.685159894Z	2022-12-24T10:00:00Z	48.248659793814454	value	solarGarageWatts	solarGarageWatts
0	2022-12-20T12:59:09.685159894Z	2023-01-19T12:59:09.685159894Z	2022-12-24T11:00:00Z	50.22012077294682	value	solarGarageWatts	solarGarageWatts
0	2022-12-20T12:59:09.685159894Z	2023-01-19T12:59:09.685159894Z	2022-12-24T12:00:00Z	42.523036876355725	value	solarGarageWatts	solarGarageWatts
0	2022-12-20T12:59:09.685159894Z	2023-01-19T12:59:09.685159894Z	2022-12-24T13:00:00Z	90.42290909090913	value	solarGarageWatts	solarGarageWatts

Now I use influx to calculate the total yield since 01.01.2023 with the integral function:

from(bucket: "openhab")
                |> range(start: 2023-01-01T00:00:00.000Z)
                |> filter(fn: (r) => r["_measurement"] == "solarGarageWatts")
                |> filter(fn: (r) => r["_field"] == "value")
                |> filter(fn: (r) => r["item"] == "solarGarageWatts")
                |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)
                |> integral(unit: 1h)
                |> map(fn: (r) => ({ r with _value: float(v: r._value) / 1000.0}))
                |> yield(name: "mean")

It’s a single value:
image

Additionally I calculate the sum of the previous day and store it in a separate bucket:
(I’m using habapp for that at the moment)

import logging
import HABApp
from influxdb_client import InfluxDBClient, Point
from influxdb_client.client.write_api import SYNCHRONOUS
from datetime import date
from datetime import timedelta

from HABApp.openhab.items import NumberItem
from HABApp.core.events import ValueUpdateEventFilter, ValueChangeEventFilter, ValueChangeEvent, ValueUpdateEvent

client = InfluxDBClient(url="http://192.168.2.3:8086", token="TOKEN", org="obi")

write_api = client.write_api(write_options=SYNCHRONOUS)
query_api = client.query_api()


log = logging.getLogger('HABApp')


class solarDailySumRule(HABApp.Rule):
    def __init__(self):
        super().__init__()

        self.run.on_sun_dawn(self.calc)


    def calc(self):
        day = date.today() - timedelta(days=1)
        startDate = day.strftime("%Y-%m-%dT00:00:00.000Z")
        stopDate = day.strftime("%Y-%m-%dT23:59:59.999Z")

        # using Table structure
        query = '''
            from(bucket: "openhab")
                |> range(start: %s, stop: %s)
                |> filter(fn: (r) => r["_measurement"] == "solarGarageWatts")
                |> filter(fn: (r) => r["_field"] == "value")
                |> filter(fn: (r) => r["item"] == "solarGarageWatts")
                |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)
                |> integral(unit: 1h)
                |> map(fn: (r) => ({ r with _value: float(v: r._value) / 1000.0}))
                |> yield(name: "mean")''' % (startDate, stopDate)

        tables = query_api.query(query)
        table = tables.pop()

        records = table.records.pop()
        print(str(day) + ": " + str(records["_value"]))

        p = Point("daily").field("_value", records["_value"]).time(day.strftime("%Y-%m-%dT00:00:00.000Z"))

        write_api.write(bucket="solarDailyStats", record=p)

solarDailySumRule()

The content of this result is displayed in Grafana:

image
image1589×252 20.1 KB

@the.neon You may go that way to fulfill your requirement. Just use the influx-query, change start to the first day of the month, stop to the last day of the month and the name of the item (currentWatts). But you have to implement the automation too.

5

Hello! I’ve successfully implemented a similar solution by connecting Shelly to Grafana using InDriver. You can check out the video demonstration here, or visit https://www.inanalytics.io to freely download InDriver. It makes building a monitoring system incredibly easy. Feel free to explore and let me know if you have any questions!