Preconditions<\/h2>\n\n\n\n
In order to use the approach described here, you should:<\/p>\n\n\n\n
- \n
- \u2026 have access to a Linux machine or account<\/li>\n\n\n\n
- \u2026 have a MySQL<\/a> or MariaDB<\/a> database server installed, configured, up and running<\/li>\n\n\n\n
- \u2026 have a populated MySQL<\/a> or MariaDB<\/a> database like in our example to which you have access<\/li>\n\n\n\n
- \u2026 have the package Grafana<\/a> [2<\/a>] installed, configured, up and running<\/li>\n\n\n\n
- \u2026 have some basic knowledge of how to operate in a Linux environment and some basic understanding of shell scripts<\/li>\n<\/ul>\n\n\n\n
Description and Usage<\/h2>\n\n\n\n
The base for the following visualizations is a fully populated database of the following structure:<\/p>\n\n\n\n
# Datenbank f\u00fcr Analysen der Solaranlage\n# V1.1; 2023-12-10, Gabriel R\u00fceck <gabriel@rueeck.de>, <gabriel@caipirinha.spdns.org>\n# Delete existing databases\nREVOKE ALL ON solaranlage.* FROM 'gabriel';\nDROP DATABASE solaranlage;\n\n# Create a new database\nCREATE DATABASE solaranlage DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;\nGRANT ALL ON solaranlage.* TO 'gabriel';\nUSE solaranlage;\nSET default_storage_engine=Aria;\n\nCREATE TABLE anlage (uid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\\\n zeitstempel TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\\\n leistung INT UNSIGNED DEFAULT NULL,\\\n energie INT UNSIGNED DEFAULT NULL);<\/code><\/pre>\n\n\n\n[1<\/a>] explains how such a database can be filled with real-world data using smart home devices whose data is then stored in the database. For now, we assume that the database already is fully populated and contains large amount of data of the type:<\/p>\n\n\n\n
MariaDB [solaranlage]> SELECT * FROM anlage LIMIT 20;\n+-----+---------------------+----------+---------+\n| uid | zeitstempel | leistung | energie |\n+-----+---------------------+----------+---------+\n| 1 | 2023-05-02 05:00:08 | 0 | 5086300 |\n| 2 | 2023-05-02 05:15:07 | 0 | 5086300 |\n| 3 | 2023-05-02 05:30:07 | 0 | 5086300 |\n| 4 | 2023-05-02 05:45:07 | 0 | 5086300 |\n| 5 | 2023-05-02 06:00:07 | 0 | 5086300 |\n| 6 | 2023-05-02 06:15:07 | 12660 | 5086301 |\n| 7 | 2023-05-02 06:30:07 | 39830 | 5086307 |\n| 8 | 2023-05-02 06:45:08 | 44270 | 5086318 |\n| 9 | 2023-05-02 07:00:07 | 78170 | 5086333 |\n| 10 | 2023-05-02 07:15:07 | 187030 | 5086367 |\n| 11 | 2023-05-02 07:30:07 | 312630 | 5086424 |\n| 12 | 2023-05-02 07:45:08 | 665900 | 5086556 |\n| 13 | 2023-05-02 08:00:07 | 729560 | 5086733 |\n| 14 | 2023-05-02 08:15:08 | 573700 | 5086889 |\n| 15 | 2023-05-02 08:30:07 | 288030 | 5086985 |\n| 16 | 2023-05-02 08:45:07 | 444170 | 5087065 |\n| 17 | 2023-05-02 09:00:08 | 655880 | 5087217 |\n| 18 | 2023-05-02 09:15:07 | 974600 | 5087476 |\n| 19 | 2023-05-02 09:30:08 | 1219150 | 5087839 |\n| 20 | 2023-05-02 09:45:07 | 772690 | 5088024 |\n+-----+---------------------+----------+---------+\n20 rows in set (0,000 sec)\n<\/code><\/pre>\n\n\n\nwhereby the values in column leistung<\/strong> represent the current power generation in mW<\/em> and the values in energy represent the accumulated energy<\/strong> in Wh<\/em>.<\/p>\n\n\n\n
Now we shall visualize the data of the solar power generation 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
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-7.png\")
MySQL connection setup in Grafana (local database)<\/figcaption><\/figure>\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
Average Power per Month and Hour of the Day (I)<\/h3>\n\n\n\n
For this, 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
SELECT MONTHNAME(zeitstempel) AS Monat, HOUR(zeitstempel) AS Stunde, ROUND(AVG(leistung)\/1000) AS Leistung\nFROM anlage\nGROUP BY Monat, Stunde\nORDER BY FIELD (Monat,'January','February','March','April','May','June','July','August','September','October','November','December'), Stunde ASC;<\/code><\/pre>\n\n\n\nThis query delivers us an ordered list of power values grouped and ordered by (the correct sequence of) month, and subsequently by the hour of the day as shown below:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-5-1024x486.png\")
Output of the MySQL query for the 2-dimensional table (view in the MySQL Workbench<\/a>)<\/figcaption><\/figure>\n\n\n\n However, this is not enough to get the table view we want in Grafana<\/a> as the power values are still one-dimensional (only one value per line). We need to transpose the table with respect to the months, and therefore, we must select the built-in transformation Grouping to Matrix<\/strong> for this visualization and enter our column names according to the image below:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-6-1024x190.png\")
Required transformation for data in order to get a 2-dimensional table<\/figcaption><\/figure>\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
- \n
- Cell options \u2192 Cell type<\/strong>: Set to Colored background<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Color scheme<\/strong>: Set to Green-Yellow-Red (by value)<\/strong><\/li>\n<\/ul>\n\n\n\n
and we need to define some Overrides<\/strong>:<\/p>\n\n\n\n
- \n
- Override 1 \u2192 Fields with name<\/strong>: Select Stunde\\Monat<\/strong><\/li>\n\n\n\n
- Override 1 \u2192 Cell options \u2192 Cell type<\/strong>: Set to Auto<\/strong><\/li>\n\n\n\n
- Override 2 \u2192 Fields with type<\/strong>: Select Number<\/strong><\/li>\n\n\n\n
- Override 2 \u2192 Standard options \u2192 Unit<\/strong>: Select Watt (W)<\/strong><\/li>\n<\/ul>\n\n\n\n
Then, ideally, you should see something like this:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Tabelle-P-1024x556.png\")
Colored table of average power per month and hour of the day<\/figcaption><\/figure>\n\n\n\n Average Power per Month and Hour of the Day (II)<\/h3>\n\n\n\n
This visualization is similar to the one of the previous chapter. However, we slightly modify the MySQL query to:<\/p>\n\n\n\n
SELECT MONTHNAME(zeitstempel) AS Monat, HOUR(zeitstempel) AS Stunde, ROUND(GREATEST((AVG(leistung)-STDDEV(leistung))\/1000,0.0)) AS '\u00d8-1\u03c3'\nFROM anlage\nGROUP BY Monat, Stunde\nORDER BY FIELD (Monat,'January','February','March','April','May','June','July','August','September','October','November','December'), Stunde ASC;<\/code><\/pre>\n\n\n\nDoing so, we assume that there is some reasonable normal distribution<\/a> in the timeframe of one month among the values in the same hourly time interval which, strictly speaking, is not true. Further below, we will look into this assumption. Using this unproven assumption, if we subtract 1\u03c3 from the average power value and use the resulting value or zero (whichever is higher), then we come to a value where we can say: “Probably (84%), in this month and in this hour, we generate at least x<\/em> watts of power.” So if someone was looking, for example, for an answer to the question on “When would be the best hour to start the washing machine and ideally use my solar energy for it?”, then, in June, this would be somewhen between 10:00…12:00, but in September, it might be 11:00…13:00, a detail which we might not have uncovered in the visualization of the previous chapter. Of course, you might argue that common sense (“Look out of the window and turn on the washing machine when the sun is shining.”) makes most sense, and that is true in our example. However, for automated systems that must run daily, such information might be valuable.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Tabelle-P-\u03c3-1024x549.png\")
Colored table of (average power – 1\u03c3) per month and hour of the day<\/figcaption><\/figure>\n\n\n\n Heatmap of the Hourly and Daily Power Generation<\/h3>\n\n\n\n
For this, we use the plug-in Grafana<\/a> visualization Hourly heatmap<\/a><\/strong>. We select the according database, and our MySQL query is very simple:<\/p>\n\n\n\n
SELECT UNIX_TIMESTAMP(zeitstempel) AS time, ROUND(leistung\/1000) AS power FROM anlage;<\/code><\/pre>\n\n\n\nThis time, there is also no need for any transformation, but again, we should adjust some panel options<\/a> in order to beautify our graph, and these are:<\/p>\n\n\n\n
- \n
- Hourly heatmap \u2192 From<\/strong>: Set to 05:00<\/strong><\/li>\n\n\n\n
- Hourly heatmap \u2192 To<\/strong>: Set to 23:00<\/strong><\/li>\n\n\n\n
- Hourly heatmap \u2192 Group by<\/strong>: Select 60 minutes<\/strong><\/li>\n\n\n\n
- Hourly heatmap \u2192 Calculation<\/strong>: Select Mean<\/strong><\/li>\n\n\n\n
- Hourly heatmap \u2192 Color palette<\/strong>: Select Spectral<\/strong><\/li>\n\n\n\n
- Hourly heatmap \u2192 Invert color palette<\/strong>: (enabled)<\/li>\n\n\n\n
- Legend \u2192 Gradient quality<\/strong>: Select Low<\/strong> (it already computes long enough in Low<\/strong> mode)<\/li>\n\n\n\n
- Standard options \u2192 Unit<\/strong>: Select Watt (W)<\/strong><\/li>\n<\/ul>\n\n\n\n
Then, ideally, after some seconds, you should see something like this:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Heatmap-1024x102.png\")
Heatmap<\/figcaption><\/figure>\n\n\n\n This visualization is time-consuming. Keep in mind that if you have large amounts of data, it might take several seconds until the graph build up. The Hourly Heatmap<\/strong> is also more detailed than the previous visualizations, and we can point with the mouse pointer on a certain rectangle and get to know the average power on this day and in this hour (if the database has at least one power value per hour). It even shows fluctuations occurring in one day only which might go unnoticed in the previous visualizations. We can realize, for example, that the lower average power which was generated in July this year was not because the sky is greyer in July than in other months, but that there were a few 2…3 days periods with clouded sky where the electricity generation dropped while other days were just perfectly sunny as in June or August. We can also see how the daylight hours dramatically decrease when we reach November.<\/p>\n\n\n\n
Daily Energy Generation, split into Time Ranges<\/h3>\n\n\n\n
The next graph shall visualize the daily energy generation of the solar system over the time, and we split the day into 4 time ranges (morning, lunch, afternoon, evening). The 4 time ranges are not equally large, so you have to pay close attention to the description of the time range. Of course, you can easily change the MySQL query and adapt the time ranges to your own needs. We use the built-in Grafana<\/a> visualization Time Series<\/strong><\/a>. We select the according database, and our MySQL query uses the timestamp and the energy values of the dataset. We also use the uid as we calculate the differences in the (ever increasing) energy value between two consecutive value sets in the dataset.<\/p>\n\n\n\n
SELECT DATE(past.zeitstempel) AS time,\n CASE\n WHEN (HOUR(past.zeitstempel)<11) THEN '05:00-11:00'\n WHEN (HOUR(past.zeitstempel)>=11) AND (HOUR(past.zeitstempel)<14) THEN '11:00-14:00'\n WHEN (HOUR(past.zeitstempel)>=14) AND (HOUR(past.zeitstempel)<17) THEN '14:00-17:00'\n WHEN (HOUR(past.zeitstempel)>=17) THEN '17:00-22:00'\n END AS metric,\n SUM(IF(present.energie>=past.energie,IF((present.energie-past.energie<4000),(present.energie-past.energie),NULL),NULL)) AS value\nFROM anlage AS present INNER JOIN anlage AS past ON present.uid=past.uid+1\nGROUP BY time,metric;<\/code><\/pre>\n\n\n\nIn order to better understand the MySQL query, here are some explanations:<\/p>\n\n\n\n
- \n
- We determine the time ranges in the form of a CASE<\/strong> statement in which we determine our time ranges and what shall be the textual output for a certain time range.<\/li>\n\n\n\n
- We sum up the differences between consecutive energy values in the database over the respective time ranges. This is the task of the SUM<\/strong> operator.<\/li>\n\n\n\n
- There might be missing energy values in the dataset, maybe because of network outages or (temporary) issues in the MySQL socket. The IF<\/strong> clauses inside the SUM<\/strong> operator help us to determine if the result of (present.energie-past.energie) would be negative (in case that present.energie is NULL) or if we have an unrealistic value (\u22654000) because past.energie is NULL. Such values will be skipped in the calculation.<\/li>\n\n\n\n
- We consult the same tables (anlage) two times, one time named as present and one time named as past whereby past the dataset immediately before present is.<\/li>\n\n\n\n
- Keep in mind that as time we use the value of past.zeitstempel<\/strong> so that we include the last value that still fits into the time range (although for the calculation of the energy difference, present.zeitstempe<\/strong>l would already be the next hour value, e.g.: (past.zeitstempel=16:45 and present.zeitstempel=17:00).<\/li>\n<\/ul>\n\n\n\n
We furthermore should adjust some panel options<\/a> in order to beautify our graph, and these are:<\/p>\n\n\n\n
- \n
- Graph styles \u2192 Fill opacity<\/strong>: Set to 45%<\/strong><\/li>\n\n\n\n
- Graph styles \u2192 Stack Series<\/strong>: Select Normal<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Unit<\/strong>: Select Watt-hour (Wh)<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Min<\/strong>: Set to 0<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Decimals<\/strong>: Set to 0<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Color scheme<\/strong>: Select to Classic<\/strong> palette<\/strong><\/li>\n<\/ul>\n\n\n\n
On the left side, just above the code window, we have to switch from Table<\/strong> to Time Series<\/strong>:<\/p>\n\n\n\n
![\"Switch](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/grafik.png\")
Do not forget to switch to the correct format.<\/figcaption><\/figure>\n\n\n\n The graph should now look like this:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Stromerzeugung-nach-Zeitbereichen-1024x210.png\")
Solar Energy according to Time Ranges<\/figcaption><\/figure>\n\n\n\n There is one interesting characteristic of the built-in Grafana<\/a> visualization Time Series<\/strong><\/a> which we make use of. If we edit the graph and select the Table view, we will see that Grafana<\/a> automatically has created a matrix from the results of our MySQL query.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/image-9-1024x324.png\")
Matrix for the stacked Time Series<\/figcaption><\/figure>\n\n\n\n This is astonishing because the MySQL query does not create the matrix itself, but rather delivers an output like:<\/p>\n\n\n\n
MariaDB [solaranlage]> SELECT DATE(past.zeitstempel) AS time,\n -> CASE\n -> WHEN (HOUR(past.zeitstempel)<11) THEN '05:00-11:00'\n -> WHEN (HOUR(past.zeitstempel)>=11) AND (HOUR(past.zeitstempel)<14) THEN '11:00-14:00'\n -> WHEN (HOUR(past.zeitstempel)>=14) AND (HOUR(past.zeitstempel)<17) THEN '14:00-17:00'\n -> WHEN (HOUR(past.zeitstempel)>=17) THEN '17:00-22:00'\n -> END AS metric,\n -> SUM(IF(present.energie>=past.energie,IF((present.energie-past.energie<4000),(present.energie-past.energie),NULL),NULL)) AS value\n -> FROM anlage AS present INNER JOIN anlage AS past ON present.uid=past.uid+1\n -> GROUP BY time,metric\n -> LIMIT 15;\n+------------+-------------+-------+\n| time | metric | value |\n+------------+-------------+-------+\n| 2023-05-02 | 05:00-11:00 | 3588 |\n| 2023-05-02 | 11:00-14:00 | 4428 |\n| 2023-05-02 | 14:00-17:00 | 2524 |\n| 2023-05-02 | 17:00-22:00 | 375 |\n| 2023-05-03 | 05:00-11:00 | 4260 |\n| 2023-05-03 | 11:00-14:00 | 6047 |\n| 2023-05-03 | 14:00-17:00 | 2648 |\n| 2023-05-03 | 17:00-22:00 | 369 |\n| 2023-05-04 | 05:00-11:00 | 6326 |\n| 2023-05-04 | 11:00-14:00 | 5867 |\n| 2023-05-04 | 14:00-17:00 | 2568 |\n| 2023-05-04 | 17:00-22:00 | 477 |\n| 2023-05-05 | 05:00-11:00 | 3113 |\n| 2023-05-05 | 11:00-14:00 | 3496 |\n| 2023-05-05 | 14:00-17:00 | 1657 |\n+------------+-------------+-------+\n15 rows in set (0,197 sec)\n<\/code><\/pre>\n\n\n\nI found out that this only works if metric is not<\/strong> a numerical value. For numerical values, the matrix would not be auto generated, the graph would not work like this. Maybe, an additional transformation in Grafana<\/a> might then be necessary.<\/p>\n\n\n\n
Detailed Examinations of the Dataset<\/h3>\n\n\n\n
Let us look closer to subsets of the dataset and visualize them, in order to get a better understanding of how the solar power generation behaves in reality at selected times.<\/p>\n\n\n\n
Power Distribution between 12:00 and 13:00 (4 values per day)<\/h4>\n\n\n\n
For this, we use the built-in Grafana<\/a> visualization Heatmap<\/a><\/strong>. We select the according database, and our MySQL query is:<\/p>\n\n\n\n
SELECT zeitstempel AS time, ROUND(leistung\/1000) AS 'Power' FROM anlage\nWHERE (HOUR(zeitstempel)=12);<\/code><\/pre>\n\n\n\nKeep in mind that in my dataset, I register 4 samples per hour in the database. This is defined by the frequency of how often the script described in [1<\/a>] is executed, and this itself is defined in crontab<\/a>. You can also have the script be executed every minute, and hence you will register 60 samples per hour in the database which will be more precise but put higher load on your server and on the evaluation of the data in MySQL queries. So, in my case, in the time frame [12:00-13:00], we consider 4 power values, and we filter for these power values simply by looking at timestamps with the value “12” as the hour part. The Heatmap will give the distribution of these 4 power values per day. We must furthermore adjust some panel options<\/a>, and these are:<\/p>\n\n\n\n
- \n
- Heatmap \u2192 X Bucket<\/strong>: Select Size<\/strong> and set to 24h<\/strong><\/li>\n\n\n\n
- Heatmap \u2192 Y Bucket<\/strong>: Select Size<\/strong> and set to Auto<\/strong><\/li>\n\n\n\n
- Heatmap \u2192 Y Bucket Scale<\/strong>: Select Linear<\/strong><\/li>\n\n\n\n
- Y Axis \u2192 Unit<\/strong>: Select Watt (W)<\/strong><\/li>\n\n\n\n
- Y Axis \u2192 Decimals<\/strong>: Set to 1<\/strong><\/li>\n\n\n\n
- Y Axis \u2192 Min value<\/strong>: Set to 0<\/strong><\/li>\n\n\n\n
- Colors \u2192 Mode<\/strong>: Select Scheme<\/strong><\/li>\n\n\n\n
- Colors \u2192 Scheme<\/strong>: Select Oranges<\/strong><\/li>\n\n\n\n
- Colors \u2192 Steps<\/strong>: Set to 64<\/strong><\/li>\n<\/ul>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Power-Distribution-between-1200-and-1300-4-values-per-day-1024x85.png\")
Power Distribution between 12:00 and 13:00 (4 values per day)<\/figcaption><\/figure>\n\n\n\n With this Heatmap, we can immediately understand an important detail, and that is: If someone had the assumption that between [12:00-13:00], we generate a lot of power every day in summer, this would clearly be very wrong. In fact, we can recognize that the power generation varies from low to high values on many days. The reason is that even in summer, there are many cloudy days in the location where this solar power generator is located. This might not be true for a different location, and if we had data from Southern Europe (Portugal, Spain, Italy), this might look very different.<\/p>\n\n\n\n
Average Power [12:00-13:00]<\/h4>\n\n\n\n
Now, we shall look at the average power generation between [12:00-13:00] over the year, using the 4 power values that we have register in this time interval every day. For this, we use the built-in Grafana<\/a> visualization Time Series<\/strong><\/a>. We select the according database, and we use two MySQL queries (A<\/strong> and B)<\/strong>. Both curves will then be visualized in the diagram with different colors. Our query A<\/strong> calculates the average value per day in the time interval [12:00-13:00] and is displayed in green color:<\/p>\n\n\n\n
SELECT zeitstempel AS time, ROUND(AVG(leistung)\/1000) AS 'Power [12:00-13:00]' FROM anlage\nWHERE (HOUR(zeitstempel)=12)\nGROUP BY DATE(zeitstempel);<\/code><\/pre>\n\n\n\nOur query B<\/strong> takes a 7d average<\/strong> of the average values per day, it therefore is kind of a trend line<\/em> of the curve we have generated with query A<\/strong> and is displayed in yellow color:<\/p>\n\n\n\n
SELECT zeitstempel AS time, (SELECT ROUND(AVG(leistung)\/1000) FROM anlage WHERE (HOUR(zeitstempel)=12) AND zeitstempel<=time AND zeitstempel>DATE_ADD(time,INTERVAL-7 DAY)) AS 'Power [12:00-13:00; 7d average]' FROM anlage\nWHERE (DATE(zeitstempel)>='2023-05-09')\nGROUP BY DATE(zeitstempel);<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Average-Power-1200-1300-1024x93.png\")
Average Power [12:00-13:00]<\/figcaption><\/figure>\n\n\n\n This graph visualizes the same finding as the previous heatmap. In my opinion, it even shows in a more dramatic way that the average power between [12:00-13:00] even during summer can vary dramatically, and that we simply cannot assume that there is a certain minimum power “guaranteed” in a certain time frame during summer. We also see that in winter, the power generation is minimal, and there is no chance that the solar power generator can contribute to the electricity demand of the home in any meaningful way.<\/p>\n\n\n\n
Average Power [12:00-13:00] (calculated as difference of energy values)<\/h4>\n\n\n\n
One might argue that 4 sampling values per hour are not enough, maybe we were just unlucky and there were clouds in the sky exactly when we were sampling the data. Let us therefore repeat the graph from above, not by using the average of the 4 samples per hour but let us divide the increase of the overall generated energy in the time frame [12:00-13:00] as the base for the power calculation. Again, we will do two queries (A<\/strong> and B<\/strong>) whereby query A<\/strong> is the value on the daily basis:<\/p>\n\n\n\n
SELECT present.zeitstempel AS time, (present.energie-past.energie) AS 'Power [12:00-13:00]'\nFROM anlage AS present INNER JOIN anlage AS past ON present.uid=past.uid+4\nWHERE (TIME_FORMAT(present.zeitstempel,'%H:%i')='13:00');<\/code><\/pre>\n\n\n\nand where query B<\/strong> shows the 7d average<\/strong> of the daily values:<\/p>\n\n\n\n
SELECT zeitstempel AS time, (SELECT AVG(present.energie-past.energie) FROM anlage AS present INNER JOIN anlage AS past ON present.uid=past.uid+4 WHERE (TIME_FORMAT(present.zeitstempel,'%H:%i')='13:00') AND present.zeitstempel<=time AND present.zeitstempel>DATE_ADD(time,INTERVAL-7 DAY)) AS 'Power [12:00-13:00; 7d average]' FROM anlage\nWHERE (DATE(zeitstempel)>='2023-05-09')\nGROUP BY DATE(zeitstempel);<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Average-Power-1200-1300-calculated-as-difference-of-energy-values-1024x91.png\")
Average Power [12:00-13:00] (calculated as difference of energy values)<\/figcaption><\/figure>\n\n\n\n While we can identify some differences in the green curve between this graph and the previous one, the yellow curve is almost identical. In general, the calculation via the energy values is the more precise one; 4 sample values simply do not seem to suffice as we can see in the differences of the green curves.<\/p>\n\n\n\n
The overall conclusion of this curve is the same as in the previous one, however: There are many days where the average power generation between [12:00-13:00] stays well below the expected (larger) values.<\/p>\n\n\n\n
Average Hourly Power Generation [June]<\/h4>\n\n\n\n
The following visualization, again using the built-in Grafana<\/a> visualization Time Series<\/strong><\/a>, shows how the average value of power generated per hour in the month of June varies if we look at the first half and the second half of June. This visualization uses three queries. Query A<\/strong> shows the average power generation of the whole month<\/strong> of June:<\/p>\n\n\n\n
SELECT zeitstempel AS time, ROUND(AVG(leistung)\/1000) AS 'Power [June 2023]' FROM anlage\nWHERE (zeitstempel>'2023-06-01') AND (zeitstempel<'2023-07-01')\nGROUP BY HOUR(zeitstempel);<\/code><\/pre>\n\n\n\n - Heatmap \u2192 Y Bucket<\/strong>: Select Size<\/strong> and set to Auto<\/strong><\/li>\n\n\n\n
- Graph styles \u2192 Stack Series<\/strong>: Select Normal<\/strong><\/li>\n\n\n\n
- We sum up the differences between consecutive energy values in the database over the respective time ranges. This is the task of the SUM<\/strong> operator.<\/li>\n\n\n\n
- Hourly heatmap \u2192 To<\/strong>: Set to 23:00<\/strong><\/li>\n\n\n\n
- Override 1 \u2192 Cell options \u2192 Cell type<\/strong>: Set to Auto<\/strong><\/li>\n\n\n\n
- Standard options \u2192 Color scheme<\/strong>: Set to Green-Yellow-Red (by value)<\/strong><\/li>\n<\/ul>\n\n\n\n
- \u2026 have a populated MySQL<\/a> or MariaDB<\/a> database like in our example to which you have access<\/li>\n\n\n\n