today I started using Grafana and everything went fine with the gauge. But then I wanted to implement a graph and my whole data is shifted for about 1 hour.
When I look into my MSSQL database I get to zero value on this timestamp:
So it is shifted by exactly 1 hour. Does it convert my data with some time zone functions or anything like that?
Also the gauge got the right (newest) value and the graph not with the same query?!
So basically data that is 1 hour old gets used with the timestamp of current time but only in graph.
Query:
SELECT
zeitstempel AS time,
taktleistung_ist AS IST_TAKT
FROM taktleistung
WHERE maschine = ‘maschine1’ AND zeitstempel >= DATEADD(day, -7, GETDATE())
ORDER BY 1 DESC
I had to limit the data to 1 week because there are more than 1 million rows.
Are you in a timezone of UTC plus or minus one hour?
What timezones are the grafana server and the pc running the browser set to?
Grafana will adjust the data in the database (which is assumed to be in UTC) to the local timezone. So either the timezone of the server or browser are wrong (in fact usually the timezone of the server will not matter) or the timestamps in the database are not in UTC put the data into the db incorrectly. Or perhaps you are adjusting the timestamp in the query.
I write into the db with an OPC UA-Client that pulls the data out of an OPC UA-Server. The timestamp I use is from the OPC UA-Server. Inside the server under “System” is a variable that is called “datetime_local”.
It has the same time as the server running Grafana.
But I think it is handled as normal UTC because of the standard with the format YYYY-MM-DDTHH:MM:SS? I googeled for the “T” between DD and HH because I thought it is an indicator for a standardized time format (ISO 8601 UTC).
My thought is, that Grafana handles the UTC +1 from the OPC UA-Server as UTC and adds +1 caused by local browser time?
Yes that sounds likely. However I think if you write it to the db correctly you can tell it to interpret it as local time and let mysql convert it to utc as it writes, I am not an expert on mysql though so don’t know exactly about that. Timestamps should always be in utc in the database otherwise when grafana asks for data around the DST change it will either get two samples for the same time or there will be a gap in the data. With the data in the db in UTC then grafana will convert it local time and should not be confused by the DST change.
If you are fetching current data from the the OPC server and immediately writing it to the db then you would probably better to use an automatic timestamp field in the db and let mysql insert it. That is ok provided you don’t mind it being a short time off due to the time taken to fetch it. It obviously won’t work if the server buffers up data over a time then sends it to you later.
I use MSSQL but I think it handles it the same way MySQL does. I can’t check it until tomorrow. But thanks for the ideas @clanlaw ! I’ll update the issue tomorrow.
In future I probably use a time-series db for that case. But you are right… I might overthink that. New to the field In the past I just queried the database and never created tables and so on.
If you have a choice for time series data then a good choice is Influxdb.
I misread your initial post and thought you were using MySQL. I imagine MSSQL is similar.
I tested it with using the normal “dateime” variable from the OPC UA-Server and not the “datetime_local”. It actually worked for me. So the “datetimelocal” variable is UTC +1 but Grafana interpreted it as UTC and added +1 hour, so the timestamp was correct but with 1 hour old data.
Solved. Thanks to @clanlaw for giving me the right idea.
In the past I just queried the database and never created tables and so on.