[SOLVED] Timezone Offset Issue in Grafana despite UTC Configuration

Timezone Offset Issue in Grafana despite UTC Configuration

I’m visualizing data from a MySQL database in Grafana where timestamps are stored in UTC (+00:00). However, when I query the database, I see the data displayed with my local timezone (+1:00). The issue I’m facing is that even though I’ve configured Grafana to use the UTC timezone, the data is being displayed with a +1:00 offset, as if Grafana is performing an implicit conversion on the data.

Here’s the SQL query I am using:

SELECT
  energy_consumption.updated_at AS "time",
  device.nome AS "metric",
  energy_consumption.sensorData AS "value"
FROM
  energy_consumption, device
WHERE
  device.idDevice = energy_consumption.idDevice
ORDER BY
  energy_consumption.updated_at

In Grafana, I have set the timezone to UTC, however, when I check the Query Inspector, I see that the timestamp is being retrieved with my local timezone (+1:00). This is because Grafana seems to be converting the data to my local timezone, even though the database stores it in UTC (+00:00).

Additionally, when I try to display data for the last 5 minutes, Grafana shows no data because it’s interpreting the timestamp as if it were in UTC, and there is no data within that time range based on the current local time.

It seems like Grafana might be performing an implicit timezone conversion on the data. Has anyone encountered this issue or knows how to fix it?

Solution

Finally, I have found this solution that worked for me. Instead of taking directly the timestamp from the database, I convert it to UTC epoch time:

SELECT
  UNIX_TIMESTAMP(energy_consumption.updated_at) * 1000 AS "time",
  device.nome AS "metric",
  energy_consumption.sensorData AS "value"
FROM
  energy_consumption, device
WHERE
  device.idDevice = energy_consumption.idDevice
ORDER BY
  energy_consumption.updated_at;