Mysql datasource: duplicate timezone offset

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!

“MySQL converts TIMESTAMP values from the current time zone to UTC for
storage, and back from UTC to the current time zone for retrieval. (This does
not occur for other types such as DATETIME.)”

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

So, change your field type to DATESTAMP and it shoudl do what you want.

Antony.

I will give that a try, thanks a lot!
I do however think that this should be possible without altering the data, as there might be use cases in which one might want to insist on a timestamp column. So this still feels like a bug to me.