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!