Grafana + MySQL timezone issue

Is there a manual on how to set up MySQL and insert data into it such that there are no timezone issues with Grafana?
I live in +2:00 timezone. Both my server and clients are in the same timezone. I feel this is the simplest possible scenario if the MySQL+Grafana are expected to work together, but I haven’t been able to resolve the timezone issue.

Please use the datetime type for the time field of the mysql, then Grafana will follow your browser as timezone. Even the presentation in the Mysql using local timezone, the datetime type should be in the timestamp value. Datetime is the one of the difficult problem to handle… :slight_smile:

Regards,
Fadjar Tandabawana

Thank you Fadjar for your response. I am using the DATETIME type for my time field:

MariaDB [logs]> describe `records`;
+-----------+----------+------+-----+-------------------+-----------------------------+
| Field     | Type     | Null | Key | Default           | Extra                       |
+-----------+----------+------+-----+-------------------+-----------------------------+
| record_id | int(11)  | NO   | PRI | NULL              | auto_increment              |
| sensor_id | int(11)  | NO   |     | NULL              |                             |
| time      | datetime | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| value     | double   | NO   |     | NULL              |                             |
+-----------+----------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

When I insert values into it, I convert them into UTC time first, as recommended many times on this forum. So the last entry looks like this:

MariaDB [logs]> SELECT * FROM `records` ORDER BY record_id DESC LIMIT 1;
+-----------+-----------+---------------------+-----------+
| record_id | sensor_id | time                | value     |
+-----------+-----------+---------------------+-----------+
|   7880134 |         7 | 2020-05-14 10:41:26 | 20.493333 |
+-----------+-----------+---------------------+-----------+
1 row in set (0.01 sec)

MariaDB [logs]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-05-14 12:42:08 |
+---------------------+
1 row in set (0.00 sec)

The two-hour difference between the last value and now is due to +2:00 time zone we are in.

My plots are still missing 2 hours, although the current value is being shown at the correct time.

I agree handling time is difficult, but I don’t want to do anything outside of the basic functionality here.

Do you know what I’m doing wrong?

Is there a user manual that describes how to set up grafana with MySQL, including notes about timezone and how to insert time? I fought hard to convince people in my lab Grafana is the right choice for monitoring sensors. Now I am failing at a simple demonstration.

Many thanks!

Look at this:

Having

SELECT UNIX_TIMESTAMP(time) AS “time”, …

shifts everything back 2 hours in time.

I guess now if I don’t shift time when I insert records, everything should be fine.

I mean, again, this is just the basic functionality. I was expecting it to work out of the box. Are all the developers and testers sitting nex to the Big Ben? :slight_smile:

There is issue in grafana where it checks time as UTC time but when in interval comparison or group by it checks time as local and only converting interval time to UNIX .

Solution - You should add one more column to table which should contains UNIX equivalent time of your timestamp and pass that in time column and everything will work.

Eg.
Local Browser Time - 2020-05-31 17:21:05 (It is UTC + 5:30)
UNIX_TIMESTAMP - 1590945665