i have troubles with the grafana graph an the timetable. MySql Database shows the timestamp correctly in the table - also the graph is ok when i choose the rigth time and date - the problem is, when i´m login then graph has an offset of 2 hours.
can anybody help me …
Is the data in MySQL stored with UTC timestamps?
This is a requirement for Grafana.
thx for your answer - my datatype ist TIMESTAMP in MySQL - in TIMESTAMP there should be UTC included ??? right ?
No. You need to use DATETIME.
See https://dev.mysql.com/doc/refman/8.0/en/datetime.html “MySQL converts
TIMESTAMP values from the current time zone to UTC for storage, and back from
UTC to the current time zone for retrieval. (This does not occur for other
types such as DATETIME.) By default, the current time zone for each connection
is the server’s time.”
So, when Grafana fecthes a TIMESTAMP value from MySQL, the result will be in
your local (server’s) timezone, which is probably not UTC.
Grafana needs to get the result back in UTC.
my first chioce was DATETIME and it was the same issue
Were you placing UTC values into the DATETIME field?
Also check in the Dashboard Settings (the cog in the top panel of the dashboard), on the General tab that you have Browser Time for the Timezone.
my table with entries is ok - just the graph time on axis has an offset of 2 hours - when i change the timezone in dashboard then it´s also incorrect
i made a screenshot from the database and the csv export from grafana graph
i´m not a profi so im thankful for your help
Can you provide a screenshot of the time on the axis still wrong please.
There is no point worrying about the data until the graph is showing the correct current time at the right hand end when set to last 3 hours or simiilar.
i have found something - the column timestamp is in format UTC+02:00 - when i go to one entrie an do under functions UTC TMESTAMP then everything is ok - the problem is my device only write timestamp include +02:00 - is any tip for a workaround ?
thanks for help
In the excel image you posted earlier you showed, for example,
2020-04-27T14:01:12+02:00 is 14:01 the correct local time, and the timezone is gmt+2?
yes - also in phpmyadmin the timestamp is correct - when i use function for utc timestamp then the time switch to utc and grafana is working fine - but i have to do this for all entrys thats not possible to do this
What do you mean by that?
What exactly do you see in phpmyadmin?