Hi,
I have a mysql table with a time column that has correct data in it, but the data points are shifted +2 hours on my dashboards. The current data is displayed 2 hours in the future.
My time zone is CEST (+02), my browser time zone is “Europe/Helsinki”.
MySQL data:
The data has the correct timestamp in MySQL. These queries were executed at ~09:52 CEST = ~07:52 UTC:
MariaDB [sensors]> SET time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [sensors]> select * from esp8266 order by ts desc limit 3;
+-------------+----------+---------------------+---------+
| temperature | humidity | ts | sensor |
+-------------+----------+---------------------+---------+
| 25 | 46.7 | 2019-06-27 07:51:49 | sensorA |
| 29.1 | 36.9 | 2019-06-27 07:49:21 | sensorB |
| 24.9 | 47.3 | 2019-06-27 07:46:49 | sensorA |
+-------------+----------+---------------------+---------+
3 rows in set (0.00 sec)
MariaDB [sensors]> SET time_zone='+02:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [sensors]> select * from esp8266 order by ts desc limit 3;
+-------------+----------+---------------------+---------+
| temperature | humidity | ts | sensor |
+-------------+----------+---------------------+---------+
| 25 | 46.7 | 2019-06-27 09:51:49 | sensorA |
| 29.1 | 36.9 | 2019-06-27 09:49:21 | sensorB |
| 24.9 | 47.3 | 2019-06-27 09:46:49 | sensorA |
+-------------+----------+---------------------+---------+
3 rows in set (0.00 sec)
The ts timestamp field is of type " timestamp not null default CURRENT_TIMESTAMP".
Dashboard
Grafana version is 6.2.4, the server hosting grafana + mysql is configured to CEST timezone.
The following applies to both timezone settings “Default” and “Browser time”:
Query run at 10:01 CEST for “Last hour”:
SELECT
ts AS “time”,
humidity AS “sensor A”
FROM esp8266
WHERE
ts BETWEEN FROM_UNIXTIME(1561618868) AND FROM_UNIXTIME(1561622468) AND
sensor = ‘sensorA’
ORDER BY ts
The timestamps translate to 1561618868 =>
UTC : Thursday, June 27, 2019 7:01:08 AM
CEST : Thursday, June 27, 2019 9:01:08 AM
and 1561622468 =>
UTC : Thursday, June 27, 2019 8:01:08 AM
CEST : Thursday, June 27, 2019 10:01:08 AM
The query returns some datapoints but displays none of them. The dashboard is empty. Result:
name:“sensor A”
points:Array[12]
0:Array[48.9,1561626409000]
1:Array[48.3,1561626709000]
2:Array[47.8,1561627009000]
3:Array[48,1561627309000]
4:Array[46,1561627609000]
5:Array[46.7,1561627909000]
6:Array[46.2,1561628209000]
7:Array[46.7,1561628509000]
8:Array[47.3,1561628809000]
9:Array[46.7,1561629109000]
10:Array[45.5,1561629409000]
11:Array[45.6,1561629709000]
When setting the dashboard timezone to UTC i don’t see datapoints either, but I have not checked the query / response for that.
The only way I can see the data is by changing the time window to now-1h to now+2h.
For a query run at 10:01 CEST for now-1h to now+2h I will see datapoints starting from 11:01 CEST to 12:01 CEST. The duration makes sense, because “now-1h” requests one hour of data. It’s just that the data should be displayed from 09:01 CEST to 10:01 CEST.
Reason: MySQL already gives the datapoints converted to the client’s timezone and Grafana seems to add another +2 (== correct timezone offset) on top of it. This might be happening because the server hosting Grafana is set to CEST and Mysql might pick up on that.
Example:
The query returned datapoint 4: [46,1561627609000]
The timestamp translates to
UTC : Thursday, June 27, 2019 9:26:49 AM
CEST : Thursday, June 27, 2019 11:26:49 AM
but it should be 07:26 UTC / 09:26 CEST.
In the database the value looks like this:
MariaDB [sensors]> SET time_zone='+02:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [sensors]> select * from esp8266 where sensor = 'sensorA' and humidity = 46 and temperature = 25;
+-------------+----------+---------------------+---------+
| temperature | humidity | ts | sensor |
+-------------+----------+---------------------+---------+
| 25 | 46 | 2019-06-27 09:26:49 | sensorA |
+-------------+----------+---------------------+---------+
1 row in set (0.00 sec)
MariaDB [sensors]> SET time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
MariaDB [sensors]> select * from esp8266 where sensor = 'sensorA' and humidity = 46 and temperature = 25;
+-------------+----------+---------------------+---------+
| temperature | humidity | ts | sensor |
+-------------+----------+---------------------+---------+
| 25 | 46 | 2019-06-27 07:26:49 | sensorA |
+-------------+----------+---------------------+---------+
1 row in set (0.00 sec)
My suggestion for a fix would be to set the connection timezone in grafana to UTC before running the query.
My question to the forum is: did I miss something? Is there a configuration that I can change? Thank you!