Hello Grafanists,
I am new here and beginner in using Grafana. I have made some visualizations with rrdtool and now I come to a new generation of display. Thanks for the work done on Grafana!
My task seems quite simple, but I can’t reach the goal for weeks:
My MariaDB collects data from three energy meters every minute. The visualization should be done with a line graph for the last 1000 readings and update every minute.
For this I built this query:
SELECT timestamp, phase_all_avrg_power FROM Energieverbrauch.Wohnungsanschluss WHERE timestamp > NOW() - INTERVAL 1000 minute.
With this I get from the MySQL_CLI:
±--------------------±---------------------+
| timestamp | phase_all_avrg_power |
±--------------------±---------------------+
| 2023-08-28 23:02:01 | 184.95 |
| 2023-08-28 23:03:01 | 184.6 |
…
| 2023-08-29 15:40:01 | 114.55 |
| 2023-08-29 15:41:02 | 122.75 |
±--------------------±---------------------+
961 rows in set (0.245 sec)
Remark:
There are 961 rows because a few records were lost during experimentation.
In Grafana, this query gives me this:
“timestamp”, “phase_all_avrg_power”
2023-08-29 01:02:01,185
2023-08-29 01:03:01,185
…
2023-08-29 17:40:01,115
2023-08-29 17:41:02,123
At the time of the query, it is 15:41 CEST.
I see that Grafana rounds the measurement value. OK.
But I also see that Grafana adds two hours to the timestamp.
Most likely the offset comes from the time zone (CEST = UTC + 2h), but I can’t find where to correct it.
Basically Grafana knows the local time, because the resulting visualization ‘Last 12 hours’ shows a window that ends at 15:41 on the right.
I thank you for tips on the correction!
Boris
PS:
Grafana is running on a RaspberryPi with RaspberryOS Bookworm,Apache, PHP 8.2.
MariaDB 10.11 is running on another RaspberryPi with Raspbian Bookworm. Both systems’ location is set to Europe/Berlin. The CLI-Query is done form the Webserver.
PPS: The Grafana profile is set to default (CEST).