Alright, here we go.
1. READ THIS FIRST
I’m sharing the solution and code here so that other community members can use this as an insipration and modify it for their needs. I’m intentionally not publishing this via github because I do not have possibility to provide long term support based on other people’s feature requests. I’m responding to the questions in this thread for the time being and clarify these instructions based on the feedback.
I have used quite a lot of time for writing these instructions, so I kindly ask you to carefully re-read this comment marked as “solution” before asking for support. If you feel that you have followed every step and still haven’t figured it out, please increase the log level to see the debug level logs. If you’re still not able to figure out what is boiling down, ask for support in this thread, I’ll be glad to help.
Disclaimer
This solution is provided as an inspiration for other community members. I disclaim all warranties and responsibilities if you use this solution. In no event shall I be liable for any direct or indirect damages resulting of use this solution. High voltage connections must always be designed and performed by an authorized electrician.
License
All code below is provided without any warranty and published under the ISC License, see the code. Please attribute Markus Sipilä, Markus Sipilä - Espoo, Uusimaa, Finland | Professional Profile | LinkedIn, if you use the code.
2. WHAT ARE WE TRYING TO ACHIEVE
Spot priced electricity contract means that the price of 1 kWh is different on every hour of the day. The prices are determined by Nordpool and the day-ahead prices for the next day are published at 12.45 CET / CEST.
Because there is a significant difference in the spot prices during the day, the point is to schedule as much of the power consumption to the cheapest hours and try to minimize the consumption during the most expensive hours.
The screenshot below illustrates the result of this optimization. The blue bars represent the consumption of our house (in kWh), whereas the green line illustrate the spot price of that hour (c / kWh). As you can see, our consumption peaks systematically when the prices are at lowest.
This is achieved by scheduling the usage of electricity. Two biggest energy consumers in our house are
- Heating of the house. Our house has a Nibe F-1226 ground source heat pump that warms the water for an underfloor heating system.
- Heating the hot tap water. In addition to the integrated 180 liter water tank in the heat pump, we also have a separate 300 liter waterheater that heats the water.
Nibe heat pumps have connections for external controls, which are designed for this kind of controlling. This includes only 3.3V low voltage connections, as described later in this tutorial. I use a relay to tell the heat pump when the compressor is blocked and when it is allowed to run normally. There are two wires connected to the heat pump. All this relay does is that it connects these two wires together, which will block the heat pump compressor.
The waterheater does not have external controls. Luckily the waterheater is a super simple device, so I will simply cut the power supply from it when I don’t want it to be on. When the power supply is on, the device will simply use its own thermostat to determine when it will actually start and stop. The water heater uses 3 x 230V power supply and consumes way more power than the relay board could handle, so a small modification was made to the electrical cabinet by an authorized electrician. The relay controls a contactor, which allows or blocks the power supply to the waterheater. Details are explained below.
3. CONCEPTUAL DESCRIPTION
My Raspberry Pi is connected to a relay board so that I can control the relays with GPIO. I use Waveshare relay board with 8 relays: https://www.waveshare.com/wiki/RPi_Relay_Board_(B) This relay board can be mounted to a DIN-rail so it was very easy to attach to the box.
Side note: Even though I use the GPIO controlled relay board, the same thing can be achieved also by using smart relays, see for example https://www.shelly.cloud
Raspberry Pi and connected to a relay board
Waterheater
One of the GPIO controlled relays control the contactor for the waterheater. The contactor allows / cuts the power input for the waterheater. My openHab logic allows the power supply during the cheapest hours of the day and blocks the power supply on other hours. The contactor controls 3 phases of 230V voltage. I repeat my warning: The 230 voltage connections must only be done by an authorized electrician. High voltage can kill you.
There are a couple of ways to do the connections between the Raspberry relay board (or smart relay) and the contactor. Leave the detailed design and implementation of the 230V connections to an authorized electrician, the illustrations below are just conceptual examples.
Water heater hardware connections: option 1
GPIO controlled relay controls a contactor. The contactor is located in the electrical cabinet and it allows / cuts the power supply for the water heater. The downside of this connection option is that the connection between the Raspberry relay board and the contactor is 230 V. You absolutely must have a hard cover box and ensure that there is no voltage in these cables every time before you open the hard cover box of your Raspberry and relay board.
I originally had this connection option so I printed a warning sticker to the cover box with a reminder which fuse I need to turn off before opening the cover, see picture below.
Hard cover box with a warning sticker.
Water heater: option 2
A safer connection option is to have a second relay in your electrical cabinet so that the connection from the GPIO controlled relay board to this second relay is only 24V. This way you don’t need to bring 230V cables to the box of your Raspberry and relay board. My setup was updated to this connection option (again by an authorized electrician) for safety reasons.
Waterheater contactor in the electrical cabinet
The black component in the picture above is the contactor, which allows / cuts the power supply for the water heater. Installed by an authorized electrician.
Ground source heat pump
My ground source heat pump (Nibe F1226-8) has two external inputs. When pins 3-4 are connected, that means that AUX1 input is enabled. When pins 5-6 are connected, that means that AUX2 is enabled. The meaning of AUX1 and AUX2 inputs can be configured in the menu of the Nibe heat pump. There is for example an option “block comperssor” and “block hot water”, see the “Nibe installation manual”, it’s only described there but not in the “user guide” manual. This is a low voltage (3.3V) connection so you can test the Nibe side of this simply by using a short copper wire and connect pins 3 and 4 for AUX1 and pins 5-6 for AUX2.
My GPIO controlled relay connects / disconnects pins 3-4 as illustrated below. I have configured AUX1 to mean that the compressor is not allowed to run. This way my openHab logic can choose when the house heating is allowed and when not.
Failsafe considerations
Consider which way you want the Raspberry relay to work. In my case the connections have been made so that when the relay pulls, the power supply for the water heater is blocked. If Raspberry is completely offline and the relay does not pull, power supply is allowed.
Winters can be “quite” cold here in Finland so I need a quick way to get the waterheater and ground source heat pump to behave as if the Raspberry did not exist. This failsafe needs to be so simple that other family members can control it if I’m not at home. For this reason, the electrician installed these switches to the electrical cabinet. “LVV” is a Finnish abbreviation for “waterheater”. The first switch can be used to decide if Raspberry can control the contactor or if the power supply is always on as if the Raspberry did not exist.
Failsafe switches in the electrical cabinet
4. OPENHAB THINGS, BINDINGS, CHANNELS AND ITEMS
Warning: Do not have the physical cabling connected to your relay board when you are experimenting and building the openHab solution. The relays make a loud click when their state changes and there is also a green led indicating when the relay pulls so you can easily notice if your on/off state changes work.
If you’re new to openHab, you might want to read this conceptual background first: Concepts | openHAB
Prerequisites:
- Install openHab GPIO binding and pigpio-remote, see GPIO - Bindings | openHAB
- GPIO Binding itself can be installed at Settings - Bindings like any other openHab Binding.
Create an openHab Thing called the Waterheater and use GPIO Binding
Give a unique name for the Thing and remember to set the Network Address as ::1 like mentioned in the GPIO binding documentation linked above.
Create a Channel, configure it to use GPIO Digital Output and configure the GPIO Pin number that matches the relay that you will be using. The Waveshare relay board that I use have its GPIO pin numbers documented at RPi Relay Board (B) - Waveshare Wiki. The GPIO Pin 5 matches to the first relay of the board.
Finally, add a link to an Item to this Channel. Pay attention to the name WaterheaterPower, it will be used in the Rules later on. The type of this Item is Switch as illustrated in the picture below.
You should now be able to toggle the relay on and off using the switch Item “WaterHeaterPower” you just created. You can also find your newly created Item in the Items menu.
A hint at this point. If you installed openHabian SD image, you can use Frontail log viewer at port 9001 of your Raspberry using your web browser. You can see log entries when the state of your Item and Thing change.
Because I also want to control the Nibe ground source heat pump, I repeated the steps above and
- created a Thing called HeatPump
- created a Channel called Compressor, configured it to use GPIO output 21
- linked the Channel to a new Item called “HeatPumpCompressor”, which is of type Switch. Also pay attention to the name of this Item, it is used in the Rules below.
Testing of relay state changes and introduction to openHab Rules and Persistence
You should now be able to change the state of the relays using the openHab Items called “WaterHeaterPower” and “HeatPumpCompressor”. If you can hear the loud clicks and see the green leds turn on/off, everything works as expected.
This also means that everything is set for creating the openHab Rules that automatically change the states of these Items. If you’re new to openHab, read this first: Rules - Introduction | openHAB
This solution uses Influx2 database to store the data. openHabian OS comes with influx database server pre-installed but you can run it on any server. I run InfluxDB on my NAS server so that the data is physically stored in a more reliable place than the SD card of the Raspberry.
Once your influx database is up and running and you can log in to the Influx Data Explorer
- install the openHab InfluxDB Persistence addon from Settings - Other add-ons.
- enable it at Settings - Persistence.
State change of the Items “WaterHeaterPower” and “HeatPumpCompressor” should now be visible in your Influx Data Explorer, like in the picture below.
If you’re new to openHab, read more about data persistence here: Persistence | openHAB
In the next chapters:
- we will fetch next days spot prices from Entso-E API and store them to an Influx2 database
- calculate a control value (1 or 0) for every hour of the next day to determine if the Item should be on/off during that hour. We save these control values to the Influx2 database.
- create Rules that toggle the Items on / off based on the control value for that hour
5. FETCHING THE NORDPOOL DAY-AHEAD SPOT PRICES FROM ENTSOE-E API
This chapter describes how the Nordpool day-ahead spot prices can be fetched from Entso-E API and stored to an Influx2 database.
Pre-requisites
- The JSScripting addon must be installed. The rules are written as ECMAScript 262 Edition 11. Note the version 11.
- XSLT Transformation addon must be installed.
- JSONPath Transformation addon must be installed.
- You must have your influxDB2 up and running.
- The InfluxDB Persistence addon must be installed, for normal persistence reasons like described above. Writing the future-timestamped measurement points is done directly via the InfluxDB HTTP API, though.
- xml2json.xsl must be downloaded from Convert XML to JSON using XSLT – Bojan Bjelic and it must be saved to
/etc/openhab/transform/xml2json.xsl . Ensure that the openhab user has read access to this file.
- Copy the javascript files from below and save them to
/etc/openhab/automation/js/node_modules/kolapuuntie (change the name of the directory if you want, but remember to reflect the change in your code)
Create a Rule “FetchSpotPrices”
- Copy the code below as the Script Action (ECMAScript 262 Edition 11).
- The script invokes entsoe.js, which fetches the spot prices from Entso-E API and writes the spot prices to Influx2 database as “spot_price” measurement points with timestamps in the future via Influx HTTP API v2
- Entso-E publishes the spot prices for next day in the afternoon. date-helper.js module has logic that if the script is executed at 14:00 or later, it will fetch tomorrow’s prices. If the rule is executed before that, it will fetch current day’s prices. The script can be executed multiple times, possible previous database points are overwritten.
- You need to request a personal API access token from Entso-E and update it to this script action.
- If you’re interested in some other bidding zone than Finland, change the bidding zone code to this script action.
- If the VAT tax rate is not 24% in your country, change that as well.
- Modify your influxDB connection parameters directly to the influx.js. After you have made changes to the js files, you need to re-save the Script Action (the snippet below) to make sure openHab re-reads the js file.
- Run the rule manually and check from your influxDB data explorer that you can see the spot prices for today / tomorrow (depending on the time of the day when you executed the script). If you run the rule in the afternoon or evening, the script will fetch tomorrow’s spot prices. Remember to choose a date range in Influx data explorer which includes the day you just fetched the prices for.
- If you’re not able to see the “spot_price” measurement data in your influxDB data explorer, increase the log level to see the debug level logs
- After you are able to fetch the spot prices, schedule the Rule to run in the afternoon. You might want to schedule the rule to run for example at 14.00 and 15.00 so that if the first attempt is not able to fetch the prices, the second hopefully works.
Script action for fetching the spot prices:
dh = require('kolapuuntie/date-helper.js');
entsoe = require('kolapuuntie/entsoe.js');
influx = require('kolapuuntie/influx.js');
// Entso-E bidding zone.
zone = '10YFI-1--------U';
// Entso-E API access token.
token = 'insert-your-access-token-here';
// Multiplier for VAT
tax = 1.24;
// Get date range in the correct format for Entso-E API
start = dh.getEntsoStart();
end = dh.getEntsoEnd();
// Read spot prices and write them to the database.
points = entsoe.getSpotPrices(start, end, zone, token, tax);
influx.writePoints('spot_price', points);
The below screenshot is from the InfluxDB data explorer and it shows the spot prices in Finland on 2022-12-22.
A couple of gotchas
- Entsoe-E API returns the spot prices in XML format. For easier handling, we transform the XML to JSON with the xml2json.xslt transformation as mentioned above.
- Entso-E API seems to take input parameters in CET/CEST but responds in UTC.
6. CONTROLLING THE WATERHEATER
Now that you have the spot prices available in your influx database, you want to
- Find the cheapest hours for the waterheater
- Toggle the waterheater on/off when needed
Determining the cheapest hours for the waterheater
This script action will find the cheapest 4-hour window and write “waterheater_control” points to the InfluxDB. Value 1 means that the waterheater should be on and 0 means that it should be off. There is a point for every hour, either 1 or 0.
- Note that the name of the influx “measurement” is “waterheater_control” and not “WaterHeaterPower”. The “WaterHeaterPower” is the actual time when the openHab Item called WaterHeaterPower has been on / off. The measurement “waterheater_control” are the control points in the future when the water heater should be automatically toggled on / off.
- In the script action below, we will find the cheapest 4 hour window. Change this parameter to a value of your choice, but use a value high enough so that you waterheater can reach the thermostate max temperature to avoid legionella bacteria.
- This Script Action can either be added as a second Action in the “FetchSpotPrices” Rule or you can create a Rule of its own for this (as long as you run this rule AFTER the spot prices are saved to your influxDB).
- date-helper.js determines the start and stop midnights based on when the script is executed. Same today vs. tomorrow logic applies that I used with Entso API (cutoff time is at 14.00).
- Run the Rule manually and check from the InfluxDB data explorer that you are able to see the “waterheater_control” measurement points. If you’re not able to see them, increase your log level to DEBUG and check the logs.
Script action for determining the on/off hours for the waterheater:
dh = require('kolapuuntie/date-helper.js');
wh = require('kolapuuntie/waterheater.js');
influx = require('kolapuuntie/influx.js');
start = dh.getMidnight('start');
stop = dh.getMidnight('stop');
// Determine cheap hours and write control values to the database
hours = 4;
points = wh.determineHours(start, stop, hours);
influx.writePoints('waterheater_control', points);
Hourly script to toggle the waterheater on/off based on the control points
- You now have the “waterheater_control” points in your influxDB for every hour, either 1 or 0.
- Create a rule “WaterheaterPower” that runs on every full hour and executes the script action below.
- This script action will read the currently ongoing hour’s “waterheater_control” point from the InfluxDB
- If the “WaterHeaterPower” Item is currently ON and the “waterheater_control” point has value 0 for the hour that has just started, the WaterHeaterPower item will be set to OFF. And vice versa, if the WaterHeaterPower needs to be set to ON.
dh = require('kolapuuntie/date-helper.js');
wh = require('kolapuuntie/waterheater.js');
influx = require('kolapuuntie/influx.js');
start = dh.getCurrentHour();
control = influx.getCurrentControl('waterheater_control', start);
waterheater = items.getItem("WaterheaterPower");
// Check if the waterheater is off and should be turned on.
if (waterheater.state == 'OFF' && control == 1) {
waterheater.sendCommand('ON');
console.log('Waterheater ON.');
}
// Check if the waterheater is on and should be turned off.
else if (waterheater.state == 'ON' && control == 0) {
waterheater.sendCommand('OFF');
console.log('Waterheater OFF.');
}
else {
console.log('Waterheater: No state change needed.');
}
7. CONTROLLING THE GROND SOURCE HEAT PUMP
The logic for the waterheater above was quite simple because all we wanted to achieve is to find a window of N cheap hours. However, the heating of the house is a bit more complex because it depends on outside temperature.
- The logic below works so that we first determine the number of needed heating hours based on a weather forecast. When it’s -20 celsius or colder, 22 hours will be required (we can avoid 2 most expensive hours). When it’s +20 celsius or hotter, 2 hours will be required for heating the hot water. Every house is different, so modify this heat curve directly in the js-code based on your experiments.
- The logic for the waterheater was to find a cheapest N-hour window to ensure that the waterheater can reach the max temperature of its internal thermostat. The heat pump, on the other hand, can run for an hour, then be blocked for an expensive hour and then continue again.
- In the context of heating the house, we want to avoid a situation where the heat pump is allowed to be on for say 12 hours in a row and then blocked for the next 12 hours, because the house might cool down too much during the 12 blocked hours. To avoid this, we can split the day to a desired amount of “slices”, for example 2 x 12 hour slices or 3 x 8 hour slices. For every slice, we can define a minimum percentage of the “on” hours.
- Example: Let’s say 10h of heating is required and the day is split into 2 x 12 hour slices. We can define that at least 10% of the heating hours must be allocated to each slice. In this case both slices would have at least 0.1 x 10h = 1 hour of heating.
- The slices and percentages are configurable in the script action.
Fetching weather forecast from the Finnish Meteorology Institute’s API and saving them to the database
- Rule “FetchWeatherForecast” has the following script action.
- This action invokes fmi.js which fetches the weather forecast from FMI’s API and writes the forecasted temperatures to Influx2 database as “fmi_forecast_temperature” measurement points with timestamps in the future.
- It also fetches the wind speed and calculates the “feels like” wind chill compenstaed temperature for advanced users.
- The script can be executed multiple times a day, possible previous database points are overwritten.
Script action:
fmi = require('kolapuuntie/fmi.js');
influx = require('kolapuuntie/influx.js');
// Place recognized by Finnish Meteorology Institute's API.
place = 'veini';
// Read weather forecast and write them to the database.
xml = fmi.makeApiCall(place);
Temperature = fmi.preparePoints(xml, 'Temperature');
influx.writePoints('fmi_forecast_temperature', Temperature);
WindSpeedMS = fmi.preparePoints(xml, 'WindSpeedMS');
influx.writePoints('fmi_forecast_WindSpeedMS', WindSpeedMS);
WindChillTemp = fmi.calculateWindChillTempPoints(Temperature, WindSpeedMS);
influx.writePoints('fmi_forecast_WindChillTemp', WindChillTemp);
Determining the cheapest hours for the ground source heat pump
- This Script Action can either be added as a third Action in the “FetchSpotPrices” Rule or you can create a Rule of its own for this (as long as you run this rule AFTER the spot prices are saved to your influxDB and the weather forecast has been fetched).
- This script action invokes nibe.js which will first read the “fmi_forecast_temperature” measurement points from the Influx database and determine the number of needed heating hours.
- Once the number of needed heating hours has been calculated, it determines the allowed / blocked hours for the heat pump and saves them as “nibe_control” measurement points to the InfluxDB.
- The script action below splits the day to 3x8 slices and each slice must have 10% of the heating hours.
dh = require('kolapuuntie/date-helper.js');
nibe = require('kolapuuntie/nibe.js');
influx = require('kolapuuntie/influx.js');
start = dh.getMidnight('start');
stop = dh.getMidnight('stop');
t = nibe.getForecastTemp(start, stop);
n = nibe.calculateNumberOfHours(t);
slices = 3;
min_heating = 0.1;
points = nibe.determineHours(start, stop, n, slices, min_heating);
influx.writePoints('nibe_control', points);
Hourly script to toggle the ground source heat pump on/off based on the control points
- Create a rule that runs on every full hour and executes the script action below.
- This script action will read the currently ongoing hour’s “nibe_control” point from the InfluxDB
- If the HeatPumpCompressor item is currently ON and the “nibe_control” point has value 0 for the hour that has just started, the HeatPumpCompressor will be set to OFF. And vice versa, if the heat pump needs to be set to ON.
dh = require('kolapuuntie/date-helper.js');
nibe = require('kolapuuntie/nibe.js');
influx = require('kolapuuntie/influx.js');
start = dh.getCurrentHour();
control = influx.getCurrentControl('nibe_control', start);
compressor = items.getItem("HeatPumpCompressor");
if (compressor.state == "ON" && control == 0) {
compressor.sendCommand('OFF');
}
else if (compressor.state == "OFF" && control == 1) {
compressor.sendCommand('ON');
}
else {
console.log('Heat pump compressor: No state change needed.');
}
8. HELPER MODULES USED IN THE SCRIPT ACTIONS ABOVE
All code below is provided without any warranty and published under the ISC License, see the code. Please attribute Markus Sipilä, Markus Sipilä - Espoo, Uusimaa, Finland | Professional Profile | LinkedIn, if you use the code.
These files are placed at /etc/openhab/automation/js/node_modules/kolapuuntie. Change the name of yor module as you see best and modify the paths accordingly in the code.
Remove the .txt file extension which was required to upload the files here.
influx.js.txt (7.2 KB)
waterheater.js.txt (3.2 KB)
nibe.js.txt (7.3 KB)
fmi.js.txt (4.4 KB)
entsoe.js.txt (3.7 KB)
date-helper.js.txt (3.2 KB)
Update the influx.js with connection parameters to your InfluxDB2 instance.
Make sure that the linux file permissions are defined so that the openhab user is able to read them.
9. NOTES ON SOFTWARE COMPONENTS
- I’m running openHab 3 on a Raspberry Pi 4, with Openhabian 32-bit OS
- I’m running InfluxDB2 on my NAS, but you could run it also in the Raspberry. If you run InfluxDB on your Raspberry, be aware that the SD card will wear out at some point because of many write operations and will eventually get corrupted. Search for the community posts on this topic.
- I’m running Grafana on the same Raspberry Pi that runs openHab. It comes pre-installed with openHabian SD-image.
- I use Tailscale VPN for remote access so that I can use the browser of mobile phone even when I’m not at home. Tailscale client comes pre-installed with openHabian SD-image but it’s very straight forward to install if you don’t use openHabian OS. Never ever expose your openHab directly to the public internet. Read more on options for secure remote access: Securing Communication and Access | openHAB
10. USER INTERFACES
You are of course welcome to build your user interfaces as you like, but here some screenshots of my UI as an inspiration. If you’re new to openHab, you can read more about Pages at Pages - Introduction | openHAB
Overview page
Screenshot of my overview page is illustrated below. Translations of the Finnish UI labels:
- Olohuone = Living room. Displays the room temperature. The temperature measurements are not in the scope of this tutorial.
- Sisäilman kosteus = Humidity. Displays the humidity, not in scope of this tutorial. I use the Vallox binding to get the measurement from our air conditioning unit.
- Sähkön hinta nyt = Spot price now. I have created an Item with the exact same name “spot_price” that is used by the script that fetches the spot prices from Entso-E API. I have an hourly script that updates the Item so that I can render the Item in the UI. See the script action below the image.
- Ulkolämpötila = Outside temperature. Taken from the Vallox air conditioning unit. Not in the scope of this tutorial.
- Custom widget that displays the spot prices of today. Built with openHab Charts functionality.
- I have two items that control the rules. Kotona = At home and Automaattiohjaus = Automatic control. The “At home” switch controls the air conditioning unit (among other things) and “Automatic control” is used in the hourly scripts for the waterheater and ground source heat pump. If the Automatic control is ON, the hourly scripts are toggling the waterheater / heat pump on / off based on the control points. If the automation is “off”, I can manually control them using the switches below.
- Below these two, I have switches for the waterheater, heat pump, air conditioning unit and other things.
Script action for an hourly rule that updates the spot_price Item so that I can render it in the UI:
dh = require('kolapuuntie/date-helper.js');
influx = require('kolapuuntie/influx.js');
// Update the spot price Item with the price of the current hour
now = dh.getCurrentHour();
current_price = influx.getCurrentControl('spot_price', now);
spot_price = items.getItem('spot_price');
spot_price.postUpdate(current_price);
Spot prices and control values with Grafana
I originally built a visualization for spot prices vs. control values with Grafana as illustrated in the screenshot below. If you use openhabian OS, grafana is pre-installed for you, see openHABian | openHAB
The Grafana dashboard is then embedded to an openHab “Layout” page as an WebFrame widget. I have the forecasted temperature for today, spot prices for today, calculated control points for the waterheater and for ground source heat pump and the spot prices as a table. Below that, there is the average spot price for today. The second tab of the openHab “Tabbed Page” provides the same data for tomorrow.
To get the URL that you can embedd to the WebFrame, Grafana allows you to “share” a dashboard or panel like this:
Note that the time range is not “locked”. You can re-use the same dashboard for “today” and “tomorrow” or any other time range by appending the desired relative date range in the Grafana URL, for example like this: http://192.168.1.33:3000/d/27zjm2R4z/tuntihinnat-ja-ohjaukset?from=now/d&to=now/d&orgId=1&kiosk
Flux-queries for the graphs:
Query for the spot prices:
from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "spot_price" and
r._field == "value"
)
|> map(fn: (r) => ({ _value:r._value, _time:r._time, _field:"Sähkön tuntihinta (c/kWh)" }))
And for the control value:
from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "nibe_control" and
r._field == "value"
)
|> map(fn: (r) => ({ _value:r._value, _time:r._time, _field:"Maalämpöpumpun kompressori" }))
Average price of the day (change the visualization as “Stat”)
from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "spot_price" and
r._field == "value"
)
|>mean()
To render the spot prices as a table, change the visualization as “Table” in Grafana.
Spot prices and control values as openHab Charts Page
I recently migrated from the Grafana based approach to an openHab Charts Page solution so that I can more flexibly navigate between days. My current page looks like this. The gray Step Line / Area represents the spot prices (and the dotted gray line the daily average price), the red Step Line / Area the heating control values and the blue Step Line / Area the waterheater control values. Additionally I’m rendering the forecasted temperature and its average.
Read more about Chart Pages: Chart Pages | openHAB The openHab Charts are essentially Apache ECharts. The chart configurations in the openHab UI are quite basic, but you can utilize all ECharts options by modifying the chart definition YAML code. For a reference on the configuration options, for example for Areas or Step lines, see Get Started - Handbook - Apache ECharts
Control parameters page
I sometimes want to fine tune the automatically calculated control points. For this reason, I have have a Tabbed page called “Control parameters”, which has a tab for heating, water heater and our Air Heat Pumps (I control our Air Heat pump using the MelCloud Binding, not in the scope of this tutorial):
- Lämmitystuntien määrä: Number of heating hours
- Vuorokauden jako lämmitysosiin: Number of heating slices
- Force heating ON for a given hour
- Force heating OFF for a given hour
I have modified the rules so that I have an Item called “HeatingHours” and “HeatingSlices”. My own implementation is slightly more complex than illustrated in the tutorial section above:
- I calculate the number of heating hours based on the weather forecast and update the HeatingHours item. I can adjust the value also manually using this UI.
- The script action that calculates the control points for the ground source heat pump reads the number of needed hours from the HeatingHours item instead of calculating it on-demand as illustrated in the tutorial section above.
- If the number of heating hours or heating slices is updated here, it triggers a re-calculation of the control points.
- The force-on and force-off items have a datetime input widget. If these are changed, it triggers a rule that sets the control point for that specific hour as 1 or 0.
Energy consumption vs spot prices
I fetch the actual power consumption from Caruna using GitHub - kimmolinna/pycaruna: Caruna API for Python (not in the scope of this tutorial) and store the consumption to InfluxDB as “caruna_consumption” measurement. I have also created a dummy Item called “caruna_consumption” to openHab with the exact same name, which means I can access the values in Charts Page using this Item. Here is the consumption of one week, blue bars is consumption and green line is the spot price. Note the date range slider at the bottom and week navigation on the top. I like this navigation flexibility as opposed to building this with Grafana and embedding to openHab as a WebFrame.
Consumption Factor
Finally, I have a Grafana dashboard where I can see the spot price average of the defined date range and see how much below our actual consumption is. The calculation formula here is the same as is used in Väre Välkky, Helen Fiksusähkö and Fortum Duo contracts, where you have a base price but you can then affect the final price with scheduling the consumption to the cheap hours.
Query for the average spot price, change the visualization to “Stat”:
from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => (r["_measurement"] == "spot_price"))
|> filter(fn: (r) => (r["_field"] == "value"))
|> mean()
Query for the consumption factor (“kulutusvaikutus”), visualize as “Stat”
avg_price =from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => (r["_measurement"] == "spot_price"))
|> filter(fn: (r) => (r["_field"] == "value"))
|> mean()
|> findColumn(fn: (key) => key._measurement == "spot_price", column: "_value")
total_consumption =from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => (r["_measurement"] == "caruna_consumption"))
|> filter(fn: (r) => (r["_field"] == "value"))
|> sum()
|> findColumn(fn: (key) => key._measurement == "caruna_consumption", column: "_value")
caruna_consumption = from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "caruna_consumption" and r["_field"] == "value")
spot_price = from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "spot_price" and r["_field"] == "value")
join(tables:{caruna_consumption:caruna_consumption, spot_price:spot_price}, on:["_time"])
|> map(fn:(r) => ({
time: r._time,
_value: ((r._value_spot_price - avg_price[0]) * r._value_caruna_consumption) / total_consumption[0],
})
)
|> sum()
Query for the bar chart, visualize as Time Series, bar chart:
avg_price =from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => (r["_measurement"] == "spot_price"))
|> filter(fn: (r) => (r["_field"] == "value"))
|> mean()
|> findColumn(fn: (key) => key._measurement == "spot_price", column: "_value")
total_consumption =from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => (r["_measurement"] == "caruna_consumption"))
|> filter(fn: (r) => (r["_field"] == "value"))
|> sum()
|> findColumn(fn: (key) => key._measurement == "caruna_consumption", column: "_value")
caruna_consumption = from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "caruna_consumption" and r["_field"] == "value")
spot_price = from(bucket: "openhab")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "spot_price" and r["_field"] == "value")
join(tables:{caruna_consumption:caruna_consumption, spot_price:spot_price}, on:["_time"])
|> map(fn:(r) => ({
time: r._time,
Kulutusvaikutus: ((r._value_spot_price - avg_price[0]) * r._value_caruna_consumption) / total_consumption[0],
})
)
