Grafana and MySQL Time Issues

Grafana MySQL
1

I have a Grafana instance set to UTC. The data in a MySQL database is stored as a datetime (in UTC).

I have a query to obtain data using:
WHERE $__timeFilter(time) AND …

The MySQL database server is set to local time. So when the MySQL query executes, I retrieve data, but not the most recent 5/6 hours (I am in Central time), which corresponds to our UTC offset. From what I found in doing a search, the solution is to set the MySQL database time zone to UTC. Our center’s MySQL server is used by many others and unfortunately that isn’t an option.

I did find in MySQL you can set it for a session (e.g., SET @@session.time_zone = “+00:00”;), but my Grafana MySQL query doesn’t seem to let me do that command and then the query.

Is there a way to do two SQL commands within the Grafana dashboard or another way to resolve this time issue?

Thanks,
Justin

2

Imperfect workaround: add time offset to the time field:

SELECT DATE_ADD(timestampcolumn, INTERVAL 5 HOUR) ....

Check doc for correct syntax and time function.

3

Good idea, thank you! Unfortunately I cannot add the time offset how I’d need.

A subset of my grafana query is:
…
WHERE $__timeFilter(time) AND…
…
which expands to
…
WHERE time BETWEEN FROM_UNIXTIME(1562061242) AND FROM_UNIXTIME(1562104442) AND…
…

But I do not create the expanded SQL, it is a grafana macro that uses the GUI start and end times. I basically need to somehow add the 5/6 hours to the second (end) time, but I do not think that is possible (without manually going to the GUI and for the end time selecting now+6h which isn’t realistic because users of the dashboard wouldn’t know to do that.

Anyone have ideas how to add that to the end time using the grafana macro?

4

I would like to see a solution to this problem implemented as I feel it should be supported.

In the meantime, I have found an acceptable work around, in addition to the UTC date/time stored in my DB for the data, I’ve added a timestamp variable to the DB that is autopopulated (and due our center’s MySQL time setting to local time, it is also in local time).

In my Grafana MySQL query, I retrieve the UTC date/time but the where clauses operates on the timestamp variable. Not ideal but I’ve at least been able to display current data correctly.

e.g.,

SELECT
  time as time_sec,
  data as data
FROM myDataBase
WHERE $__timeFilter(time_stamp)
1 Like
5

You sir are a brilliant man. I hated SQL in College, I still hate SQL now. Thanks for that workaround :slight_smile: