I have installed Grafana on my home server in a docker container. I also have MySQL in another container. The time zone of the host is correctly set to CET and shows the correct time.
The MySQL container is by default set to UTC, and therefore shows time one hour behind. The now() function in SQL also shows one hour to late. I need now() to store the correct time in the database so I therefore changed the timezone inside the docker container. It now shows correct time and SQL now() is also correct.
Now the problem is that the time series in grafana shows data shifted one hour into the future. If I display last three ours it looks like this:
I tried to change the grafana docker container also to CET but it makes no difference. have tried to change the time zone in the data source, but no change. And the time zone in the panel. It just shows different times at the X axis, but still everything is shifted one hour to the right.
I have also googeled a lot but not found a solution that works.
Use UTC everywhere - data in the MySQL must be saved in the UTC. Grafana will “move” data in your graphs to your browser timezone by default (but you can still configure dashboard time zone in the dashboard configuration).
1 Like
If the database has to be in UTC I will consider this a bug in Grafana. The timezone setting in the data source should take care of this.
Pushing real time measurement data to a database using now() in the SQL will work if the database is in UTC, but if you also store data by setting the time manually in the SQL there will be an error. In my case I have a script that reads power prices from a web API and store them for the coming 24 hours in localtime (CET) in the database.
Welcome to the world of the time zones. UTC is a golden rule. Of course you don’t need that, but then be prepared for all this kind of issues. It sounds easy, but isn’t. Especially if you have users in mutiple timezones and those timezones may have or may not have Daylight Saving Times. Did you known that DST starts in Europe on March 27, 2022, but in USA on 13 March? How will your CEST data graph looks like on March 27? I guess you will have a gap. So if you resist on data in CET, then good luck. You have to fiddle with time offsets, SQL client time zones, query time zones, … and there still will be a chance for time zone drifts. I bet you will find that data in UTC is the best practice if you will have bigger picture - you are thinking now only in your local CET zone.
BTW good DBs have time datetime datatypes with timezones (PostgreSQL: Documentation: 9.1: Date/Time Types), that’s exactly address this issue.
1 Like
You have a good point about DST! I have wondered how to solve that. Here in Norway (CET) we go back one hour the last Sunday of October at 3am. What happens to measurements between 2am and 3am? double up?
Using UTC without DST will solve that issue!
I then just have to either shift my writing of power prices to UTC or change the SQLs in my panel
Start at the hardware and work up ensuring everything is running in the same timezone. Common gotcha is to start services in the incorrect timezone via the command line.
Double check you don’t have some weird ntp sync as well, took me an age to track that one down a few years back
Note UTC has no concept of daylight savings
