Issues with timezone

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)

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!


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).

Either make your datetime column utc or use convert_tz type of function to make your datetime utc

Hej yosiasz,
you made my day!
Query is now
SELECT convert_tz(timestamp,‘Europe/Berlin’,‘UTC’),Phase_all_avrg_power FROM Stromverbrauch.Wohnungsanschluss WHERE timestamp > NOW() - INTERVAL 1440 Minute
and everything lokks fine with this.
Thank you very much!