__timeFilter does not produce the correct time range in SQL

Grafana MySQL
1

My server and my sensors that collect data are in Switzerland (CEST timezone +0200). I store all the dates in UTC timestamp format in the database. It is Sep 9, 2019 13:22pm. When I click on “Generated SQL”, I see the following time filter:
WHERE time BETWEEN FROM_UNIXTIME(1568006519) AND FROM_UNIXTIME(1568028119)
The time filter I have selected is for the last 6 hours. If I check those times on the server, I get the following:

SELECT FROM_UNIXTIME(1568028119), FROM_UNIXTIME(1568006519);
| 2019-09-09 13:21:59 | 2019-09-09 07:21:59 |

But when I look at the graph, the data starts from 4 hours ago, and not 6 hours ago. The gap between 6 and 4 hours ago is empty, even though I do have data there. The range on the x-axis is fine though.
What am I doing wrong?

Some additional command outputs.
$ date Mon Sep 9 13:31:04 CEST 2019 $ timedatectl Local time: Mon 2019-09-09 13:35:24 CEST Universal time: Mon 2019-09-09 11:35:24 UTC RTC time: Mon 2019-09-09 11:35:24 Time zone: Europe/Zurich (CEST, +0200) System clock synchronized: yes systemd-timesyncd.service active: yes RTC in local TZ: no!

Capture|606x500

2

UPDATE:
I changed the datatype in mysql from TIMESTAMP to DATETIME. I still have the same problem.
I also completely removed grafana and reinstalled it. I had to reconfigure everything again, so I am pretty sure I removed all configuration and database files. Still have the same problem.

3

UPDATE 2:
I changed the timezone on the Ubuntu server, where mysql and grafana are hosted, to UTC, and now the time filter is working normally. This, however, is not an acceptable workaround for me.

4

There are plenty of topics on the issue, but I’ve found no nice solution except maybe this hook for MySQL. Do you happen to know if Grafana can be provided MySQL URL in provisioning which can include time zone setting for MySQL? E.g.

datasources:
  url: mysql://localhost:3306/grafana_db?time_zone="+00:00"
5

This is the simplest use-case for grafana and mysql. My conclusion is, grafana does not work with MySql unless your server is in the UTC timezone. I don’t understand this!

6

I have the same issue with Grafana:

  • Server, sensors and browser are in German timezone
  • datatype in MySQL is DATETIME
  • Time is logged in UTC by sensors
  • As expected the actual time is converted correct to the local timezone
  • When using macro “$__timeFilter(column)” data is missing

To fix the issue I use this filter:
timeUTC BETWEEN convert_TZ($__timeFrom(),@@SESSION.time_zone,'+00:00') and convert_TZ($__timeTo(),@@SESSION.time_zone,'+00:00')

In my opinion the timezone conversion is missing in the macro “$__timeFilter(column)”.

1 Like
7

thank you for this solution - works great for me!

this really needs to be fixed MySQL Timezone/Grafana Interface Issue · Issue #18120 · grafana/grafana · GitHub