Grafana not handling timezone correct for MariaDB

I am having trouble with the timezone offset in grafana.

I have a MariaDB 10 database with DB fields of type DATETIME I use as grafana time column.
I am located in timezone UTC+2 (CEST)
However in grafana when selecting tle latest 3 hours I get only 1 hour of data (see screenshot 1)
The filter in itself looks good when I inspect (inspection performed on 24/4/2022 19:25 CEST)
SELECT measurementtime AS “time”, sensorpurpose AS metric, celsius FROM temperatures
WHERE measurementtime BETWEEN FROM_UNIXTIME(1650810305) AND FROM_UNIXTIME(1650821105)
ORDER BY measurementtime

The inspection result in grafana however adds 2 hours to the time (see screenshot 2).

How can I fix this issue?
Note: I had to merge some screenshots because I have a 2 image limit on this site.

afbeelding

You say you are using a DATETIME field and that you are located in timezone
UTC+2.

What timestamps are you putting into the database: UTC, or UTC+2 (local time)?

Timestamps in a data store for Grafana should always be in UTC. If you are
recording local time, this is the cause of your problem.

Antony.

How can I verify what timestamps are in the mariaDB?

I insert the data using the mysql.connector python module
There I use executemany and the time value has type datetime.datetime

sql = “INSERT INTO “+tablename+” (measurementtime,celsius,sensorid,sensorpurpose,importtime)”
“VALUES (%s ,%s ,%s ,%s ,%s)”
mycursor.executemany(sql, insertset)

The (raspberry pi) linux machine that runs the python has /etc/timezone set to Europe/Brussels

FYI: I don’t know if it’s relevant but my mysql.time_zone table is empty.

FYI: This is what an export via phpMyAdmin or HeidiSQL shows

CREATE TABLE temperatures (
pk int(11) NOT NULL,
measurementtime datetime DEFAULT NULL,
celsius double DEFAULT NULL,
sensorpurpose varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
sensorid varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
importtime datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO temperatures (pk, measurementtime, celsius, sensorpurpose, sensorid, importtime) VALUES

(278546, ‘2022-04-24 20:10:00’, 15.62, ‘air’, ‘3c01d075a1df’, ‘2022-04-24 20:10:04’);

These are timestamps of my timezone

FYI: I have just tried switching from DB fieldtype DATETIME to DB fieldtype TIMESTAMP but this has the same result.

By using the UNIX_TIMESTAMP SQAL function I believe my database values are correct (see screenshot 1)

FYI: I have UTC+2 specified in the grafana time range popup. If I do this the times on the X axis are correct (see screenshot 2)

Maybe switching from DB fieldtype DATETIME to DB fieldtype TIMESTAMP is
better for grafana.

I seriously doubt that that would be an improvement - quoting from the MySQL
documentation: TIMESTAMP - MariaDB Knowledge Base

“If a column uses the TIMESTAMP data type, then any inserted values are
converted from the session’s time zone to Coordinated Universal Time (UTC)
when stored, and converted back to the session’s time zone when retrieved.”

Thus you can insert local timezone values and they will get stored as UTC, but
then when you retrieve them, they return to local timezone again, and you have
not achieved anything useful.

Other than that I would not want to have a database which saves values
internally which are different from what I wrote to it.

Is there an advised DB type?

The (strong) recommendation is to use the DATETIME field type, and always to
insert UTC values.

Aside from anything else, how is storing local timezone values supposed to
work when summer time ends, and you have two occurrences of, for example,
02:35:47 on the same day? These two identical local time values are different
when expressed in UTC.

Antony.

Thanks for the info.
I will use datetime.
Can you elaborate on what exactly I should do?
Should I change some mariadb global setting (specifying utc+0 for all datetime fields?)
Should I simply feed other datetime’s to my insert statements?
Maybe both?
Something else?

FYI: Based on my test with UNIX_TIMESTAMP I conclude that the timestamp in the DB is actually correct. Of course it could be that the UNIX_TIMESTAMP function knows about the timezone and does a conversion while reading from the DB. If you know a better way of determining what is actually stored in the DB then that info is appreciated.

My recommendation is to ensure that your Python script which is inserting the
timestamps does so using UTC. You may wish to adjust manually the historical
timestamps already in the database.

Antony.

Even when I put UTC time in the database grafana does not display the data correctly.
The problem is that it applies the wrong filter times so I am always missing 2 hours.

In my last attempt I created a double column where I put the UNIX_TIMESTAMP value and pass that as grafana time field.
This appears to work as grafana can interprete the double as an UTC date

Thanks,
Frederic

Looks like I will have to create a view or a redundant column to work with mariadb+grafana

1 Like

I am able to plot it using following function in sql server

SELECT
  DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), measurementtime) as time,
  celsius as value,
sensorpurpose as metric
FROM
  temperatures
ORDER BY
  measurementtime ASC

EDITED!

I can confirm I have had this exact issue. Took me forever to find as I’m new to Grafana (which I just love so far otherwise :slight_smile: )

From my testing the issue appears to be in the parsing of the variables when generating the SQL to query the DB. And also how returned dates are expected to be encoded (time zone wise) Here’s some debugging recreation info:

Ad the following to any Query on any panel:

/*

DEBUG: FROM = ${__from:date:YYYY-MM-DD HH+mm+ss}
DEBUG: TO = ${__to:date:YYYY-MM-DD HH+mm+ss}

*/

Note: I did not use capital HH for hours and got the US standard 12h time. Took me some time to get past that :wink:

Note2: Since : is not allowed by Grafana luckily + can be used in MariaDB to partition the time part. For some reason I ran into issues if I omit them :wink:

Save and use your dashboard.

Select a time interval. for example:
image

When the panel has reloaded click the title and in the menu select inspect > query

(had to remove image as I’m not allowed more that two images per post :crazy_face:)

You can now see what the parser has parsed as values for your selected interval. In my case:

/*

DEBUG: FROM = 2022-04-28 00+00+00
DEBUG: TO = 2022-04-28 23+59+59

*/

So far so good (hopefully) you get the correct dates to the parser and you can use them in your query.

Now, when the datetimes are returned in a recordset from the server, the datetimes are, in my case based on the system time zone, i.e. Europe/Stockholm. But it appears Grafana is expecting datetimes to be in UTC time zone and thus “converts” the raw datetimes in the recordset from UTC to [insert your selected time zone here]… I can somehow see this making sense as you would need it to be this way if you have several locations in several time zones looking at the same data. But, since my data is already in the local time format I need to convert the datetime to UTC before returning it to Grafana.

In MariaDB this is done with the CONVERT_TZ() function
https://mariadb.com/kb/en/convert_tz/

On my system I use Europe/Stockholm as default so I can use the SYSTEM time zone for conversion. I set up an example like this:

SELECT
    CAST(MIN(time_info) AS VARCHAR(255)) as time_min_char
    , CAST(MAX(time_info) AS VARCHAR(255)) as time_max_char
    , MIN(time_info) time_min_date
    , MAX(time_info) time_max_date
    , CONVERT_TZ(MIN(time_info), 'SYSTEM', 'UTC') time_min_date_UTC
    , CONVERT_TZ(MAX(time_info), 'SYSTEM', 'UTC') time_max_date_UTC
  • The first two rows shows the “raw” returned date from the DB, since it is explicitly cast as a VARCHAR it is not handled as a date by Grafana (apparently).
  • The third and forth row shows what happens if we simply return a date right out of the DB. Apparently Grafana “expects” all dates be UTC thus it converts the date to match the selected time zone as if they were UTC datetimes.
  • So since we have datetimes in Europe/Stockholm time, we need to convert them in the recordset to UTC before returning them to Grafana using CONVERT_TZ()

That way I get the following results, which I’m good with :slight_smile:

image

System info:
Grafana 8.4.4 as an addon installation to Home Assistant on a raspberryPi 4

  • System Time Zone: Europe/Stockholm
    MariaDB 10.4.19
  • Default Time Zone: SYSTEM
    Browser: Chrome (several incl, dev on a Chromebook all the same)

Note: Since the time zone data is not included from the start in MariaDB, you’ll need to populate those tables. If you, like me, are running MariaDB in a Docker container on a Home Assistant Raspberry Pi, here are some instructions since it took me forever to figure out how to do it https://community.home-assistant.io/t/time-zones-in-mariadb-add-on-in-container

1 Like