Dear All,
I’m having issues with fetching time series data from Mysql into my Grafana V9.3.6 dashboard.
In my MariaDB table I have a field called: CurDate of type datetime. Each time a row is inserted this field is updated using UTC_TIMESTAMP(3). This stores the the time in UTC.
My timezone is UTC +2:00, in the grafana dashboard this is also set up. The SQL server is also set to UTC +2:00
I’m running this query in grafana
SELECT
CurDate AS “time”,
VarValue AS ‘Temperature’
FROM log_data
WHERE
$__timeFilter(CurDate) AND VarId = 1
Where grafana changes the timefilter to:
CurDate BETWEEN FROM_UNIXTIME(1681982338) AND FROM_UNIXTIME(1681993138) AND VarId = 1
1681993138 equals in UTC Thursday 20 April 2023 12:18:58 which corresponds to the time in my timezone of 14:18:58. Again in the database the values are stored in UTC, meaning if a value would be stored at the very same time this would be a timestamp of 2023-4-20 12:18:58
The problem is that the FROM_UNIXTIME function is adding up again the 2 hours difference of the timezone. Now there is no data in my table as this is 2 hours in the future compared to the UTC timezone.
Changing the time in the SQL database to actual time (+2 hours) solves the issue with grafana. But only if I substract 2 hours afterwards, like this:
SELECT
DATE_SUB(CurDate, INTERVAL 2 HOUR)
VarValue AS ‘Temperature’
FROM log_data
WHERE
$__timeFilter(CurDate) AND VarId = 1
However this gives issue when changing from summer to wintertime, and during the change from summer to wintertime I can have rows with identical timestamp in the table so I prefer to keep everything in UTC in my DB.
How to properly fix this ?
1 Like
Welcome
What does UTC_TIMESTAMP(3) mean? Is it exact utc timezone or utc time zone + something
Welcome @vanbussum !
There are a number of ways this can be fixed, but if you have the option to, it’s best to put Grafana in UTC time for the same reason as the database is put in UTC time. Generally it’s good practice to have all machines in UTC time so there are no such mismatches, and then have queries change timezones to something more local when (contextually) you know the user’s timezone and distinct needs.
It’s possible to use Grafana transformations to modify the timezone on values that come back, but this won’t help if Grafana is adjusting the time filters based on a different timezone. There, Grafana can’t do better probably than use the time that it knows, which isn’t UTC.
So I think the solutions likely boil down to:
- Configure the Grafana to UTC so there’s no mismatch (preferred)
- Accept that Grafana queries will be coming from UTC+2, and adjust the SQL of the query for what Grafana expects
Is this really accurate? I cant imagine 1000s of people using grafana doing this change on each machine in a network
1 Like
I was referring to setting the grafana instance’s timezone to UTC, not to any of the thousands of machines that feed data to grafana to UTC.
In any multi-machine collection though, if there are differences in timezones between the two machines, either one end or the other has to do the reconciliation. I admit it isn’t always in every case feasible, but if you can standardize machines (not desktop computers/laptopts) on UTC, you can see it eliminates the need for either side to make adjustments.
YMMV, it’s not 100% applicable, but yes it’s usually good to set time to UTC unless there’s a reason not to. The distinction is between “what instant is it now” (everyone should agree, globally) vs. “what are the differences between two times” (contextual, location dependent). Is there a difference between 1PM in Berlin and 12PM in the UK? Actually no, but only by agreeing on UTC can they (without knowledge of the other) agree they are in the same instant in time.
Suppose you don’t do this. You have an arrangement of 1,000 machines, all with different timezones. There isn’t a good general solution here other than “know which timezone each machine is in, and do the math on one side or the other, every time”. 
2 Likes
Dear Yosiasz,
UTC_TIMESTAMP(3) gives milisecond precision.
