What Grafana version and what operating system are you using?
Grafana v10.1.5 and it is running on Windows 11.
What are you trying to achieve?
I am trying to visualize data from MySQL database and I am successful in collecting data from MySQL database into grafana.
How are you trying to achieve it?
I am sending data from node-red to MySQL database server running on windows PC and I’m visualize this data using grafana
What worked correctly?
When i manually define the time-range for e.g., “2023-12-14 16:54:23 to 2023-12-12
16:54:36”, then data is shown correctly on Line-chart and x-axis(time-axis) is also correctly shown.
What created problem?
However, if i change the time-range to “last 15 minutes/30 minutes” …, then my line chart in this case is shifted by 05:30hr behind. This problem is limited to line-chart only
What did i tried?
I configured the time-zone to UTC +05:30 inside Line-graph(edited) which now correctly shows the time in x-axis in line chart[as visible in image]. But problem is that no data is showing.
Using the gear-icon in the Grafana dashboard, i changed the time-zone to “UTC +05:30” which correclty set the time-axis of line-chart only but no-data is visible and also misconfigured the time-column of tabular chart moving it forward by -05:30hr i.e., [see image].
Some notable points:
→ In each image, i have also attested the My-SQL table as well.
→ I check for time-zone mismatch in MySQL database. For that, i ran " SELECT @@session.time_zone;", to which it returned “System”. and my system time-zone is UTC+05:30.
->*In SQL database table, i have used time_stamp TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, to create time_stamp column
What changed do i need to make? Any guidance or help?
I think it is UTC +05:30, because when i ran " SELECT @@session.time_zone;" or " SELECT @@global.time_zone;", It returned “System”. and my system time-zone is UTC+05:30.
Do i have to use " `SET GLOBAL time_zone = ‘+00:00’;" or something?. Please consider that i’m not from software background. So, my grasp of these things is limited at the moment.
Hi @yosiasz, as per your suggestion, I managed to make it work some-how!!!. However, it requires some-tweaking[Its more of a workaround than a solution.] . You tell me if this is will a good-practice or not.
→ Clearly the problem here is MySQL database which in my case is running with ‘UTC+05:30’ time-zone.
Solution: Step-1) On the Grafana[localhost:3000], Click on Profile
Step-2) In Profile window, change the time-zone to ‘Browser time’ or ‘IST’ [time-zone where your Grafana is running in my case it is running in India] and My windows PC is also running at IST. After that click on SAVE.
Step-3) Next on the dashboard screen, choose line-chart[example as shown below]. As its visible, time-stamp(along x-axis) and variable(along y-axis) is currectly matching the value as per MySQL workbench table. However, there is one problem that Line-chart is continuously running beyond the time “19:07:26” even though our MySQL database table is upto time “19:07:26”[as shown in image-1]. Also, if you click on “Table view”, you’ll find that timestamp data is +05:30hr ahead [as shown in image-2]. To remove this error, follow next-step.
Step-4) To remove this error, you have to make use of CONVERT_TZ function which not-only corrects the line-chart but also corrects the table data as well [as shown in the image]. Here, this function will shift the time-stamp values behind by 05:30hr. As a result, Our Line-chart as well as tabular data both will be corrected.
