What Grafana version and what operating system are you using?
Grafana V9.2 in Official docker container.
Windows 10
Chrome browser
What are you trying to achieve?
Using the GraphQL plugin I am pulling timeseries data into Grafana. The table after the Graphql query is correctly filled with the correct timerange. The timestamps in the table are all local time, but the query is done with UTC time. I would like to correctly display the queried data in the timeseries graph.
How are you trying to achieve it?
Using grapql plugin to pull in data.
What happened?
Everything is going well except that the graph is showing the data with the incorrect time values on the x-axis.
In the screenshot below you can see that the data point has an associated time of 16:10:05 (local time) which is correct but the X value in the graph is with one hour offset.
The raw data, as seen in the query inspector window, from the data source (graphql) is correctly ISO8601 UTC timestamped (offset 0). For example: “2022-12-28T20:25:37.706814+00:00”.
The data associated with this timestamp then shows up in the table view with the local timestamp (UTC+1).
The raw response data from the query is with UTC offset.
The table (second screenshot) is converted to local time
The datapoint popup in the first screenshot is also local time (16:10:05) but most important the X-axis value corresponding to that same point is 15:10. Please see the first screenshot.
The time series time zone option for the X-axis is set to default, which is UTC+1.
I posted a question about the exact same issue just yesterday. I am pulling data from an MSSQL db and have tried modifying the datetime being returned using select mydate as dt at time zone ‘UTC’ so I’m fairly confident that SQL is returning a binary time value ‘based’ on UTC.
Any idea how how to diagnose what grafana is receiving, or rather what SQL server is sending?
A and B are equal, C is one hour behind. A seems to imply that SQL server doesn’t do any +1 -1 offsetting when you add the ‘at time zone utc’, so I’m not sure what it does do.
I’m going to have to sit down and play with the functions in SQL and try and work out what I need to do so whatever I end up doing doesn’t go wrong after the next summer time adjustment.
Maybe Grafana needs a wiki type page for each data source type so we can add gotchas when feeding it from different sources.
there is no gotchas. it works the same for all datasources (as it should, it does not vary per data source, that would be a nightmware) the time you use has to be proper UTC.
By gotchas, I mean stuff that you know and think is obvious as you are familiar with doing this all day, but things us mere mortals get caught out on.
Much of the documentation on grafana is written for people who are already working with data in the correct format. Those of us who have to write a query to pull some bit of data out of a db just end up staring at a config trying to work out what on earth it is that grafana is looking for when something isn’t working. Date formats are a good example. I’ve been using grafana for a few years, but I had never had to display hourly data until recently, which is when this problem turned up. Group by date has been good enough until now.
We all been there. Remember grafana is a visualization tool, grouping helpers are provided with it by I never use them because they just are short cuts that create generatee code under the hood. I use pure sql functions and aggregators such as group by. So any group by day hour minute or whatever is a task of the database. So if you tighten up your sql coding skills then the rest on grafana aide is easy presentation
Eventually woth this great forum and large army of kind people you will get to where you need. Keep plowing
@yosiasz, have you been able to review my last post as I am still not sure why the popup time value does not match the X-axis time value as shown in the first screenshot of this post.
