The balcony PV system gave me two numbers: what the panels produce and what flows through the grid meter. Next, I wanted both in one place, stored and drawn as nice graphs.
The setup
Three containers and one service run on my home server:
- openHAB talks to all devices and runs the rules.
- Mosquitto is the MQTT broker, mainly for the AhoyDTU.
- InfluxDB 2 stores every value. It runs directly on the host, not in a container.
- Grafana draws the graphs.
openHAB
I keep the whole openHAB configuration in text files and not in the UI. That way I can diff it, review it and put it into git.
It was not always like that. Like most people, I first clicked the MQTT broker and the Shellys together in the UI. That way they only exist in openHAB’s database: not in git, without history, and gone if the server ever has to be set up again. So I moved them into files. The steps are at the end of this section.
The Shellys have their own binding: one thing per device, addressed by its host name, so a new IP address from the router does not break anything:
Thing shelly:shellyem3:em3 "Shelly EM3" [
deviceIp="shellyem3-xxxxxxxxxxxx.lan",
updateInterval=10
]
Thing shelly:shellypro1pm:boiler "Boiler" [
deviceIp="shellypro1pm-xxxxxxxxxxxx.lan",
updateInterval=60
]
The inverters come in via MQTT, as described for the balcony PV system. Everything ends up as an item with a unit:
Number:Power DeviceAccumulatedWatts "Grid power (net)" (gPersistence) { channel="shelly:shellyem3:em3:device#accumulatedPower" }
Number:Power BoilerPowerConsumption "Boiler power" (gPersistence) { channel="shelly:shellypro1pm:boiler:meter#currentPower" }
Switch RelayOutput "Boiler" { channel="shelly:shellypro1pm:boiler:relay#output" }
Number:Power HM400_ch0_P_AC "Inverter SW power" (gPersistence) { channel="mqtt:topic:OMV-Broker:ahoy:balkon_sw_P_AC" }
Moving things from the UI into files
Back to the broker and the Shellys I had created in the UI. openHAB keeps everything created in the UI in its own database (userdata/jsondb). The files in conf/ are a second source. A thing can only come from one of them, and two things with the same ID clash. So the order matters:
- Copy the thing’s ID, the UID (for example
mqtt:broker:OMV-Broker), and use exactly this UID in the file. The items are linked to the UID, so they keep working. - Delete the thing in the UI.
- Only then save the file, or change it once more. openHAB only reloads a file when its content changes. Touching it is not enough.
- Check that the thing is ONLINE. My MQTT things were stuck in
HANDLER_MISSING_ERRORuntil I restarted openHAB.
Items behave differently. If an item exists both in the UI and in a file, the file wins. But the UI copy stays in the database, and the UI no longer lets you delete it. The API Explorer in the developer tools can, with DELETE /items/{name}.
InfluxDB
openHAB writes every update of these items into the InfluxDB bucket openhab_db. That is the raw data. The grid power, for example, arrives every 10 seconds. The connection is configured in services/influxdb.cfg:
url = https://influxdb.example.org version = V2 token = ... # org db = home # bucket retentionPolicy = openhab_db
Yes, the bucket goes into retentionPolicy and the org into db. openHAB keeps the parameter names from InfluxDB 1, even for version 2.
What gets stored is defined in persistence/influxdb.persist:
Strategies {
everyMinute : "0 * * * * ?"
}
Items {
*: strategy = everyUpdate
gPersistence*: strategy = everyMinute
}
Every item is stored on every update, and the power items in gPersistence additionally once a minute, so a graph has no gaps when a value does not change.
One trap when I moved the token into the file: openHAB stores UI settings in userdata/config/*.config, and there every = in the token is written as \=. I copied it as it was, and for 28 minutes nothing was stored. So after any change here, check that new points actually arrive.
But raw data is not what I want to look at. Answering “How much did I import yesterday?” means integrating a power curve, and doing that in every Grafana panel is slow and easy to get wrong. So a few Flux tasks inside InfluxDB calculate the derived values and write them into a second bucket, openhab_db_calc:
| Task | Writes | Runs |
|---|---|---|
| Grid energy hourly | import and export kWh per hour | every 5 min |
| PV energy hourly | PV kWh per hour, from the inverters’ daily counters | every 5 min |
| Energy 15m | grid import/export and PV kWh per quarter hour | every 5 min |
| PV yield daily | kWh per inverter and day | every few min |
| Energy balance daily | import, export, PV, self-used PV, consumption per day | every 15 min |
| Grid power 15-min average | the quarter-hour average, as the smart meter sees it | every minute |
Two rules made this reliable:
- Every task recalculates yesterday and today. Late or missing values get fixed with the next run.
- Every value is stamped with the start of its interval.
And since everything comes from the raw data, the second bucket can be deleted and rebuilt at any time.
An example: the hourly PV energy. The inverters report a “yield today” counter that starts at zero every morning, so the energy per hour is simply how much that counter increased:
from(bucket: "openhab_db")
|> range(start: date.sub(d: 1d, from: today), stop: now())
|> filter(fn: (r) => r._measurement == "HM300_ch0_YieldToday" or r._measurement == "HM400_ch0_YieldToday")
|> aggregateWindow(every: 1h, fn: max, createEmpty: false, timeSrc: "_start")
|> window(every: 1d)
|> duplicate(column: "_value", as: "total")
|> difference(keepFirst: true)
// first hour of the day, or a counter reset: count the hour in full
|> map(fn: (r) => ({r with _value: if not exists r._value or r._value < 0.0 then r.total else r._value}))
|> window(every: inf)
|> group(columns: ["_time"])
|> sum() // both inverters
// ... rename to PvEnergyHourly_kWh and write to openhab_db_calc
A nice side effect of the counter: a gap in the readings only moves energy into the next hour. Nothing gets lost, and the hours always add up to the daily yield.
The grid side has no such counter, only the power, and that is one value for both directions: positive while importing, negative while exporting. Energy is the integral of power over time. So the power is first split into import and export, and each is then integrated per quarter hour:
power =
from(bucket: "openhab_db")
|> range(start: start, stop: now())
|> filter(fn: (r) => r._measurement == "DeviceAccumulatedWatts" and r._field == "value")
quarterly = (tables=<-, name) =>
tables
|> aggregateWindow(
every: 15m,
fn: (column, tables=<-) => tables |> integral(unit: 1h, interpolate: "linear"),
createEmpty: false,
timeSrc: "_start",
)
|> map(fn: (r) => ({r with _value: r._value / 1000.0, _measurement: name})) // Wh to kWh
|> to(bucket: "openhab_db_calc")
power
|> map(fn: (r) => ({r with _value: if r._value > 0.0 then r._value else 0.0}))
|> quarterly(name: "GridEnergy15m_Import_kWh")
power
|> map(fn: (r) => ({r with _value: if r._value < 0.0 then -r._value else 0.0}))
|> quarterly(name: "GridEnergy15m_Export_kWh")
interpolate: "linear" is the important part. Without it, integral() only sees the points inside each window, and the time between the last value of one quarter hour and the first of the next is lost. That cost about 0.8 % of the energy per day.
The same trap exists for a simple average. The Shelly reports every few seconds, but not at even intervals, and mean() gives every value the same weight, no matter how long it was valid. timeWeightedAvg() weights each value by how long it was valid, like a meter does:
from(bucket: "openhab_db")
|> range(start: date.sub(d: 15m, from: current), stop: now())
|> filter(fn: (r) => r._measurement == "DeviceAccumulatedWatts" and r._field == "value")
|> aggregateWindow(
every: 15m,
fn: (column, tables=<-) => tables |> timeWeightedAvg(unit: 1s),
createEmpty: false,
timeSrc: "_start",
)
The last example is a join. I want to know how much of my PV I used myself. For each hour, that is the PV production minus what went into the grid. The two values come from different series, so they have to be joined. That works because both are stamped with the start of the hour:
selfUsed =
join.inner(
left: pv,
right: exports,
on: (l, r) => l._time == r._time,
as: (l, r) => ({_time: l._time, _value: if l._value > r._value then l._value - r._value else 0.0}),
)
The house’s consumption is then import plus self-used PV. That is how the daily balance is built, and it is also why every task stamps its values with the start of the interval: otherwise the timestamps would not match.
Time zones
InfluxDB stores everything in UTC, and that is fine. Grafana shows it in local time anyway. The only place where I have to think about it is daily totals, because there I decide where a day ends:
- Hourly values are no problem. Grafana groups them into local days or months.
- The daily energy balance is cut at midnight in Vienna (
option location = timezone.location(name: "Europe/Vienna")). A UTC day would end at 01:00 or 02:00 local time, and the night consumption would land in the wrong day. - The daily PV yield uses UTC days. That works because there is no sun between 22:00 and 02:00 UTC 🙂
I found out how easily this goes wrong while writing this post. An old task from my early days wrote the daily export as a running total, and aggregateWindow stamps its values at the end of the window. So each day’s total carried the timestamp 00:00 of the next day. A Grafana panel took the biggest value per day and summed them up, so every sunny day was counted twice. It showed 537 kWh of export since 2024, the correct number is 470 kWh. No way! The task is deleted now, and all newer ones stamp at the start.
Grafana
The dashboard I look at most is “Current Power”:
- the production of both inverters and the yield of the day
- the consumption and the energy of the day
- gauges for the current power and the inverter temperatures
- the highest quarter hour of the day and of the month
- self-consumption and self-sufficiency, for today and for the last months, calculated in the panel from the daily values, so they are right for any time range
The red band around one o’clock is an annotation. Grafana can mark time ranges on top of the graphs, and the data for that can come from a query. Here it is the switch item BoilerPeakShed, which openHAB turns on for the few minutes it keeps the boiler off so the house draws less from the grid. Why and when that happens gets its own post later in this series. Here it is only about the query, which turns every on and off into one range:
from(bucket: "openhab_db")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "BoilerPeakShed" and r._field == "value")
|> difference(keepFirst: true) // +1 when it turns on, -1 when it turns off
|> filter(fn: (r) => not exists r._value or r._value != 0)
|> elapsed(unit: 1s) // seconds since the previous change
|> filter(fn: (r) => r._value == -1)
|> map(fn: (r) => ({
_time: date.sub(d: duration(v: r.elapsed * 1000000000), from: r._time),
timeEnd: r._time,
text: "Boiler shed: " + string(v: r.elapsed) + " s",
}))
Every “off” row knows how long ago the “on” was, so start and end of the range come from one row. The toggle “Boiler peak shaving” at the top left of the dashboard switches the bands on and off.
I build the dashboards themselves in the UI, that is what Grafana is good at. The data sources come from a file in provisioning/datasources/, again with a read-only token of its own:
apiVersion: 1
datasources:
- name: InfluxDB
uid: abcd1234
type: influxdb
url: https://influxdb.example.org
isDefault: true
editable: false
jsonData:
version: Flux
organization: home
defaultBucket: openhab_db
secureJsonData:
token: "..."
The uid is the one the data source already had, because every panel refers to it. With a new one, all dashboards would lose their data.
And the best part: with years of raw data in InfluxDB, I can test a new rule against real history before it goes live.