Mysql datasource & timezone issue

Hi,

I have a weird problem on Grafana:

My server is in Europe/Istanbul timezone, which is +3 H to UTC :

[root@SDMP01 bin]# date
Tue Apr 28 12:29:43 +03 2020
[root@SDMP01 bin]# set | grep TZ
TZ=Europe/Istanbul
[root@SDMP01 bin]# ls -alrt /etc/localtime
lrwxrwxrwx. 1 root root 35 Mar 26 11:36 /etc/localtime -> /usr/share/zoneinfo/Europe/Istanbul
[root@SDMP01 bin]# date +"%Z %z"
+03 +0300

I use Mysql as datasource and on mysql ;
default timezone is Europe/Istanbul (+03:00)

mysql> SELECT @@global.time_zone, @@session.time_zone;
±-------------------±--------------------+
| @@global.time_zone | @@session.time_zone |
±-------------------±--------------------+
| +03:00 | +03:00 |
±-------------------±--------------------+
1 row in set (0.00 sec)

mysql>

/etc/my.cnf:
default-time-zone = ‘Europe/Istanbul’

I write everything in UTC.

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)
image

however, if I select any time range between now and “now-3h” , it shows no data.
I can only select anything prior to 3hr

what is the reason for this ?

1 Like

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.”

Regards,

Antony.

I realized that I was writing to mysql with -3h offset to adjust UTC data insert
unfortunately that was causing an additional -3h offset

but when I change the data in tables with a command like this:

 UPDATE tvactivity SET date = DATE_ADD(date, INTERVAL 3 hour);
Query OK, 6293 rows affected (0.09 sec)
Rows matched: 6293  Changed: 6293  Warnings: 0

this time I can get data between now and now-3h , BUT, graphs show data in +3:

now the local time is 13:04

so ,

  1. if I insert data in -3 , graphs show correct timerange but I can’t see any data if I select something between now and now-3h
  2. if I insert data normally, graphs show incorrect timerange , and I can see data when select range between now and now-3h

how can I fix this ?

I write everything in UTC.

Good.

select unix_timestamp(date) from tvactivity order by date desc limit 1;

      1588055700 |

So, you’ve asked MySQL to convert whatever is in your “date” field into a Unix
timestamp.

That timestamp 1588055700 corresponds to 2020-04-28 06:35:00

Check:

$ TZ=UTC date -d @1588055700
Tue Apr 28 06:35:00 UTC 2020

select date from tvactivity order by date desc limit 1;

2020-04-28 09:35:00 |

So, the date is coming out of your database in local time, not UTC.

This is a problem for Grafana.

Antony.

1 Like

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)

What data type does “show create table tvactivity” tell you the “time” field
has?

Antony.

> 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              |                             |
> +-------------+------------+------+-----+-------------------+-----------------------------+

so , any ideas ? why doesn’t it display correctly ?

so , any ideas ? why doesn’t it display correctly ?

The output of “describe tvactivity” which you posted shows that the “date”
field has a data type of TIMESTAMP.

As I said previously:

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.”

Regards,

Antony.

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>

Hi!
Have you found a solution for it? I’m stuck in the same problem!

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:

Greetings,