mysql> select unix_timestamp(date) from tvactivity order by date desc limit 1;
±---------------------+
| unix_timestamp(date) |
±---------------------+
| 1588055700 |
±---------------------+
1 row in set (0.01 sec)
mysql> select date from tvactivity order by date desc limit 1;
±--------------------+
| date |
±--------------------+
| 2020-04-28 09:35:00 |
±--------------------+
1 row in set (0.00 sec)
on Grafana graph I see the data is reaching my time (below) (currently it is 12:44)
however, if I select any time range between now and ânow-3hâ , it shows no data.
I can only select anything prior to 3hr
My server is in Europe/Istanbul timezone, which is +3 H to UTC :
âŠ
I write everything in UTC.
Good.
select date from tvactivity order by date desc limit 1;
What data type is âdateâ?
It should be DATETIME for MySQL.
If it is TIMESTAMP, that is most likely your problem.
See https://dev.mysql.com/doc/refman/8.0/en/datetime.html â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.) By default, the current time zone for each connection
is the serverâs time.â
thanks for your response.
please check my second post.
I realized that I am inserting wrong data
so I changed adding +3 offset
but this time I have another issue. (see on my post above)
Hm, sorry, something seems to have got truncated about my last posting. I
think itâs the slightly odd way in which this forum software eats parts of
email postings.
Iâll try again without the quotation symbols.
As I said previously:
What data type is âdateâ?
It should be DATETIME for MySQL.
If it is TIMESTAMP, that is most likely your problem.
See https://dev.mysql.com/doc/refman/8.0/en/datetime.html â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.) By default, the current time zone for each
connection is the serverâs time.â
Thanks. I took your advice and changed the datatype from timestamp to datetime. But nothing has changed.
Below youâll see the âselectâ output from table does not change.
In addition, the behaviour on Grafana also did not change.
So , that data type is not the reason of problem, I assume.
What else can I try ?
mysql> describe tvactivity;
+-------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| activeusers | int(11) | YES | | NULL | |
| throughput | bigint(20) | YES | | NULL | |
+-------------+------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> select date from tvactivity order by date desc limit 5;
+---------------------+
| date |
+---------------------+
| 2020-04-29 08:20:00 |
| 2020-04-29 08:15:00 |
| 2020-04-29 08:10:00 |
| 2020-04-29 08:05:00 |
| 2020-04-29 08:00:00 |
+---------------------+
5 rows in set (0.01 sec)
mysql> select unix_timestamp(date) from tvactivity order by date desc limit 5;
+----------------------+
| unix_timestamp(date) |
+----------------------+
| 1588137600 |
| 1588137300 |
| 1588137000 |
| 1588136700 |
| 1588136400 |
+----------------------+
5 rows in set (0.01 sec)
mysql> alter table tvactivity modify column date datetime;
Query OK, 6524 rows affected (0.21 sec)
Records: 6524 Duplicates: 0 Warnings: 0
mysql> describe tvactivity;
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | datetime | YES | | NULL | |
| activeusers | int(11) | YES | | NULL | |
| throughput | bigint(20) | YES | | NULL | |
+-------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select date from tvactivity order by date desc limit 5;
+---------------------+
| date |
+---------------------+
| 2020-04-29 08:20:00 |
| 2020-04-29 08:15:00 |
| 2020-04-29 08:10:00 |
| 2020-04-29 08:05:00 |
| 2020-04-29 08:00:00 |
+---------------------+
5 rows in set (0.02 sec)
mysql> select unix_timestamp(date) from tvactivity order by date desc limit 5;
+----------------------+
| unix_timestamp(date) |
+----------------------+
| 1588137600 |
| 1588137300 |
| 1588137000 |
| 1588136700 |
| 1588136400 |
+----------------------+
5 rows in set (0.00 sec)
mysql>
Hello,
I had a similar issue. I have determined that executing in MySQL console set global time_zone='+00:00';
and restarting Grafana helps. I think that itâs a bug and that Grafana should set this option after connecting to MySQL server if it does expect the time to be in UTC.
This appears to be connected with the following bug report:
