Background<\/h2>\n\n\n\n
On 1st<\/sup> of March 2024, I switched from a traditional electricity provider to one with dynamic day-ahead pricing, in my case, Tibber<\/a>. I wanted to try this contractual model and see if I could successfully manage to shift chunks of high electricity consumption such as:<\/p>\n\n\n\n to those times of the day when the electricity price is lower. I also wanted to see if that makes economic sense for me. And, after all, it is fun to play around with data and gain new insights.<\/p>\n\n\n\n As my electricity supplier, I had chosen Tibber<\/a> because they were the first one I got to know and they offer a device called Pulse<\/a> which can connect a digital electricity meter to their infrastructure for metering and billing purposes. Furthermore, they do have an API [1<\/a>] which allows me to read out my own data; that was very important for me. I understand that meanwhile, there are several providers Tibber<\/a> that have similar models and comparable features.<\/p>\n\n\n\n In my opinion, dynamic electricity prices will play an important role in the future. As we generate ever more energy from renewables (solar and wind power), there are times when a lot of electricity is generated or when we even see over-production, and there are times when less electricity will be produced (“dunkelflaute”). Dynamic prices are an excellent tool to motivate people to shift a part of their consumption pattern to times with a large offer in electricity supply (cheap price). An easy-to-understand example is washing clothes during the day in summer (rather than in the evening) when there is a large supply of solar energy; then, the price will experience a local minimum between lunch time and dinner time.<\/p>\n\n\n\n In order to use the approach described here, you should:<\/p>\n\n\n\n The base for the following visualizations is a fully populated MariaDB<\/a> database with the following structure:<\/p>\n\n\n\n The section Files<\/strong> at the end of this blog post provides real-world sample data which you can use to populate the database and do your own calculations and graphs. The database contains two tables. The one named preise<\/em> contains the day-ahead prices and some price level tag which is determined by Tibber<\/a> themselves according to [1<\/a>]. The second tables named verbrauch<\/em> contains the electrical energy I have consumed, and the cost associated with the consumption. zeitstempel<\/em> in both tables indicates the date and the hour when the respective 1-hour-block with the respective electricity price or the electricity consumption starts. Consequently, the day is divided in 24 blocks of 1 hour. Data from the tables might look like this:<\/p>\n\n\n\n Now we shall visualize the data in Grafana<\/a>. Grafana<\/a> is powerful and mighty visualization tool with which you can create state-of-the-art dashboards and professional visualizations. I must really laude the team behind Grafana<\/a> for making such a powerful tool free for personal and other usage (for details to their licenses und usage models, see Licensing | Grafana Labs<\/a>).<\/p>\n\n\n\n Before you can use data from a MySQL database in Grafana, you have to set up MySQL as a data source in Connections<\/strong>. Remember that MySQL is one of many possible data sources for Grafana<\/a> and so, you have to walk through the jungle of offered data sources and find the MySQL connection and set up your data source accordingly. On my server, both Grafana<\/a> and MariaDB<\/a> run on the same machine, so there is no need for encryption, etc. My setup simply looks like this:<\/p>\n\n\n\n One step where I always stumble again is that in the entry mask for the connection setup, localhost:3306<\/em> is proposed in grey color as Host, but unless you type that in, too, Grafana<\/a> will actually not use localhost:3306<\/em>. So be sure to physically type that in.<\/p>\n\n\n\n Tibber<\/a> customers who have created an API token for themselves [4<\/a>] can populate the database with the following bash<\/a> script; in this script, you need to replace _API_TOKEN<\/em> with your personal API token ( [4<\/a>]) and _HOME_ID<\/em> with your personal Home ID ([1<\/a>]). The script further assumes that the user gabriel<\/em> can login to the MySQL database without further authentication; this can be achieved by writing the MySQL login information in the file ~\/.my.cnf<\/strong>.<\/p>\n\n\n\n In my case, I call this script with cron<\/strong> once per day, at 14:45 as Tibber<\/a> releases the price information for the subsequent day only at 14:00. The script furthermore stores UTC timestamps in the database. Grafana<\/a> will adjust them to the local time for graphs of the type Time series<\/a>.<\/p>\n\n\n\n One of my first visualizations was a table that shows the price level tags for today and tomorrow. The idea behind is that I would look at the table once per day during breakfast and immediately identify the sweet spots on where I could charge the car, turn on the washing machine, etc.<\/p>\n\n\n\n For this table, we use the built-in Grafana<\/a> visualization Table<\/a><\/strong>. We select the according database, and our MySQL query is:<\/p>\n\n\n\n The query above lists one price level per line. However, in order to get the nice visualization shown above, we need to transpose the table with respect to the months, and therefore, we must select the built-in transformation Grouping to Matrix<\/strong> and enter our column names according to the image below:<\/p>\n\n\n\n Now, in order to get a beautiful visualization, we need to adjust some of the panel options<\/a>, and those are:<\/p>\n\n\n\n and we need to define some Value mappings<\/strong>:<\/p>\n\n\n\n and we need to define some Overrides<\/strong>:<\/p>\n\n\n\n In the MySQL query above, you can find the sequence<\/p>\n\n\n\n which actually transforms the UTC timestamp into the timestamp of the local time of the machine where the MySQL server resides. In my case, this is the same machine that I use for Grafana. The sub-sequence<\/p>\n\n\n\n gives – in my case – back a 2 when we are in summer time as then we are at UTC+2h, and 1 when we are in winter time as then we are at UTC+1h. This sequence was necessary for me as the Grafana<\/a> visualization Table<\/a> does not do an automatic time conversion to the local time.<\/p>\n\n\n\n In the next visualization, we will look at a timeline of the electricity price per hour and our consumption pattern. This will look like this graph:<\/p>\n\n\n\n For this graph, we use the built-in Grafana<\/a> visualization Time series<\/a><\/strong>. We select the according database, and we define two MySQL queries. The first one is named Price<\/em>:<\/p>\n\n\n\n The first query has the peculiarity that we do not only retrieve the price for each 1-hour block, but that we also calculate a rolling 2-days average over the extracted data. The second query is named Consumption<\/em> and retrieves our energy consumption:<\/p>\n\n\n\n Again, we need to adjust some of the panel options<\/a>, and those are:<\/p>\n\n\n\n and we need to define some Overrides<\/strong>:<\/p>\n\n\n\n In this graph, we can already get an indication on how well we time our energy consumption. Ideally, the peaks (local maximums<\/strong>) in energy consumption (blue line) should coincide with the local minimums<\/strong> of the electricity price (green line). As you can see with the two peaks of the blue line (charging the BEV), I did not always match this sweet spot. There might be good reasons for consuming electricity also at hours of higher prices, and some examples are:<\/p>\n\n\n\n This graph visualizes the data according to the timezone of the dashboard, so there is no need to add an offset to the UTC timestamps in the database.<\/p>\n\n\n\n In the next visualization, we look at a heatmap<\/strong> of the hourly prices and therefore, we use the plug-in Grafana<\/a> visualization Hourly heatmap<\/strong><\/a>.<\/p>\n\n\n\n The query is very simple:<\/p>\n\n\n\n\n
Preconditions<\/h2>\n\n\n\n
\n
Description and Usage<\/h2>\n\n\n\n
The Database<\/h3>\n\n\n\n
# Datenbank f\u00fcr Analysen mit Tibber\n# V1.1; 2023-10-19, Gabriel R\u00fceck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>\n# Delete existing databases\nREVOKE ALL ON tibber.* FROM 'gabriel';\nDROP DATABASE tibber;\n\n# Create a new database\nCREATE DATABASE tibber DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;\nGRANT ALL ON tibber.* TO 'gabriel';\nUSE tibber;\nSET default_storage_engine=Aria;\n\nCREATE TABLE preise (zeitstempel DATETIME NOT NULL,\\\n preis DECIMAL(5,4) NOT NULL,\\\n niveau ENUM('VERY_CHEAP','CHEAP','NORMAL','EXPENSIVE','VERY_EXPENSIVE'));\n\nCREATE TABLE verbrauch (zeitstempel DATETIME NOT NULL,\\\n energie DECIMAL(5,3) NOT NULL,\\\n kosten DECIMAL(5,4) NOT NULL);<\/code><\/pre>\n\n\n\nMariaDB [tibber]> SELECT * FROM preise WHERE DATE(zeitstempel)='2024-03-18';\n+---------------------+--------+-----------+\n| zeitstempel | preis | niveau |\n+---------------------+--------+-----------+\n| 2024-03-18 00:00:00 | 0.2658 | NORMAL |\n| 2024-03-18 01:00:00 | 0.2575 | NORMAL |\n| 2024-03-18 02:00:00 | 0.2588 | NORMAL |\n| 2024-03-18 03:00:00 | 0.2601 | NORMAL |\n| 2024-03-18 04:00:00 | 0.2661 | NORMAL |\n| 2024-03-18 05:00:00 | 0.2737 | NORMAL |\n| 2024-03-18 06:00:00 | 0.2922 | NORMAL |\n| 2024-03-18 07:00:00 | 0.3059 | EXPENSIVE |\n| 2024-03-18 08:00:00 | 0.3019 | EXPENSIVE |\n| 2024-03-18 09:00:00 | 0.2880 | NORMAL |\n| 2024-03-18 10:00:00 | 0.2761 | NORMAL |\n| 2024-03-18 11:00:00 | 0.2688 | NORMAL |\n| 2024-03-18 12:00:00 | 0.2700 | NORMAL |\n| 2024-03-18 13:00:00 | 0.2707 | NORMAL |\n| 2024-03-18 14:00:00 | 0.2715 | NORMAL |\n| 2024-03-18 15:00:00 | 0.2768 | NORMAL |\n| 2024-03-18 16:00:00 | 0.2834 | NORMAL |\n| 2024-03-18 17:00:00 | 0.3176 | EXPENSIVE |\n| 2024-03-18 18:00:00 | 0.3629 | EXPENSIVE |\n| 2024-03-18 19:00:00 | 0.3400 | EXPENSIVE |\n| 2024-03-18 20:00:00 | 0.3129 | EXPENSIVE |\n| 2024-03-18 21:00:00 | 0.2861 | NORMAL |\n| 2024-03-18 22:00:00 | 0.2827 | NORMAL |\n| 2024-03-18 23:00:00 | 0.2781 | NORMAL |\n+---------------------+--------+-----------+\n24 rows in set (0,002 sec)\n\nMariaDB [tibber]> SELECT * FROM verbrauch WHERE DATE(zeitstempel)='2024-03-18';\n+---------------------+---------+--------+\n| zeitstempel | energie | kosten |\n+---------------------+---------+--------+\n| 2024-03-18 00:00:00 | 0.554 | 0.1472 |\n| 2024-03-18 01:00:00 | 0.280 | 0.0721 |\n| 2024-03-18 02:00:00 | 0.312 | 0.0808 |\n| 2024-03-18 03:00:00 | 0.307 | 0.0799 |\n| 2024-03-18 04:00:00 | 0.282 | 0.0750 |\n| 2024-03-18 05:00:00 | 0.315 | 0.0862 |\n| 2024-03-18 06:00:00 | 0.377 | 0.1102 |\n| 2024-03-18 07:00:00 | 0.368 | 0.1126 |\n| 2024-03-18 08:00:00 | 0.275 | 0.0830 |\n| 2024-03-18 09:00:00 | 0.793 | 0.2284 |\n| 2024-03-18 10:00:00 | 1.041 | 0.2875 |\n| 2024-03-18 11:00:00 | 0.453 | 0.1217 |\n| 2024-03-18 12:00:00 | 0.362 | 0.0977 |\n| 2024-03-18 13:00:00 | 0.005 | 0.0014 |\n| 2024-03-18 14:00:00 | 0.027 | 0.0073 |\n| 2024-03-18 15:00:00 | 0.144 | 0.0399 |\n| 2024-03-18 16:00:00 | 0.248 | 0.0703 |\n| 2024-03-18 17:00:00 | 0.363 | 0.1153 |\n| 2024-03-18 18:00:00 | 0.381 | 0.1382 |\n| 2024-03-18 19:00:00 | 0.360 | 0.1224 |\n| 2024-03-18 20:00:00 | 0.354 | 0.1108 |\n| 2024-03-18 21:00:00 | 0.382 | 0.1093 |\n| 2024-03-18 22:00:00 | 0.373 | 0.1055 |\n| 2024-03-18 23:00:00 | 0.417 | 0.1159 |\n+---------------------+---------+--------+\n24 rows in set (0,001 sec)\n<\/code><\/pre>\n\n\n\nConnecting Grafana to the Database<\/h3>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-12.png\")
Populating the Database<\/h3>\n\n\n\n
#!\/bin\/bash\n#\n# Dieses Skript liest Daten f\u00fcr die Tibber-Datenbank und speichert das Ergebnis in einer MySQL-Datenbank ab.\n# Das Skript wird einmal pro Tag aufgerufen.\n#\n# V1.3; 2024-03-24, Gabriel R\u00fceck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>\n#\n# CONSTANTS\ndeclare -r MYSQL_DATABASE='tibber'\ndeclare -r MYSQL_SERVER='localhost'\ndeclare -r MYSQL_USER='gabriel'\ndeclare -r TIBBER_API_TOKEN='_API_TOKEN'\ndeclare -r TIBBER_API_URL='https:\/\/api.tibber.com\/v1-beta\/gql'\ndeclare -r TIBBER_HOME_ID='_HOME_ID'\n\n# VARIABLES\n\n# PROGRAM\n# Read price information for tomorrow\ncurl -s -S -H \"Authorization: Bearer ${TIBBER_API_TOKEN}\" -H \"Content-Type: application\/json\" -X POST -d '{ \"query\": \"{viewer {home (id: \\\"'\"${TIBBER_HOME_ID}\"'\\\") {currentSubscription {priceInfo {tomorrow {total startsAt level }}}}}}\" }' \"${TIBBER_API_URL}\" | jq -r '.data.viewer.home.currentSubscription.priceInfo.tomorrow[] | .total, .startsAt, .level' | while read cost; do\n read LINE\n read level\n timestamp=$(echo \"${LINE%%+*}\" | tr 'T' ' ')\n # Determine timezone offset and store the UTC datetime in the database\n offset=\"${LINE:23}\"\n mysql --default-character-set=utf8mb4 -B -N -r -D \"${MYSQL_DATABASE}\" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e \"INSERT INTO preise (zeitstempel,preis,niveau) VALUES (DATE_SUB(\\\"${timestamp}\\\",INTERVAL \\\"${offset}\\\" HOUR_MINUTE),${cost},\\\"${level}\\\");\"\ndone\n\n# Read consumption information from the past 24 hours\ncurl -s -S -H \"Authorization: Bearer ${TIBBER_API_TOKEN}\" -H \"Content-Type: application\/json\" -X POST -d '{ \"query\": \"{viewer {home (id: \\\"'\"${TIBBER_HOME_ID}\"'\\\") {consumption (resolution: HOURLY, last: 24) {nodes {from to cost consumption}}}}}\" }' \"${TIBBER_API_URL}\" | jq -r '.data.viewer.home.consumption.nodes[] | .from, .consumption, .cost' | while read LINE; do\n read consumption\n read cost\n timestamp=$(echo \"${LINE%%+*}\" | tr 'T' ' ')\n # Determine timezone offset and store the UTC datetime in the database\n offset=\"${LINE:23}\"\n mysql --default-character-set=utf8mb4 -B -N -r -D \"${MYSQL_DATABASE}\" -h ${MYSQL_SERVER} -u ${MYSQL_USER} -e \"INSERT INTO verbrauch (zeitstempel,energie,kosten) VALUES (DATE_SUB(\\\"${timestamp}\\\",INTERVAL \\\"${offset}\\\" HOUR_MINUTE),${consumption},${cost});\"\ndone<\/code><\/pre>\n\n\n\nEasy Visualizations<\/h3>\n\n\n\n
Price level<\/h4>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Price-level.png\")
SELECT IF(DATE(DATE_ADD(zeitstempel,INTERVAL HOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP())) HOUR))=CURDATE(),'TODAY','TOMORROW') AS Tag, HOUR(DATE_ADD(zeitstempel,INTERVAL HOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP())) HOUR)) AS Stunde, niveau\nFROM preise\nWHERE DATE_ADD(zeitstempel,INTERVAL HOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP())) HOUR)>=CURDATE() \nGroup BY Tag, Stunde;<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-13.png\")
\n
\n
\n
DATE_ADD(zeitstempel,INTERVAL HOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP())) HOUR)<\/code><\/pre>\n\n\n\nHOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP()))<\/code><\/pre>\n\n\n\nElectricity price (per kWh) and grid consumption<\/h4>\n\n\n\n
![\"Electricity](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Electricity-price-per-kWh-and-grid-consumption-1024x215.png\")
SELECT zeitstempel,\n preis AS 'price',\n AVG(preis) OVER (ORDER BY zeitstempel ROWS BETWEEN 47 PRECEDING AND CURRENT ROW) as 'rolling 2-days average price'\nFROM preise;<\/code><\/pre>\n\n\n\nSELECT zeitstempel, energie AS 'grid consumption' FROM verbrauch;<\/code><\/pre>\n\n\n\n\n
\n
\n
Heatmap of the Tibber flexible hourly prices per kWh<\/h4>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Heatmap-of-the-Tibber-flexible-hourly-prices-per-kWh.png\")
SELECT UNIX_TIMESTAMP(DATE_ADD(zeitstempel,INTERVAL HOUR(TIMEDIFF(SYSDATE(),UTC_TIMESTAMP())) HOUR)) AS time, preis FROM preise;<\/code><\/pre>\n\n\n\n