Timezone Offset Issue in Grafana despite UTC Configuration

Grafana Dashboards
1

Hi everyone,
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?

2

The timezone offset issue in Grafana, despite UTC configuration, can arise from a few potential causes:

  1. Browser Timezone: Grafana uses the browser’s local timezone by default for displaying timestamps. Ensure the browser’s timezone is set to UTC or configure Grafana to explicitly display in UTC via the settings.
  2. Data Source Timezone: Some data sources (e.g., Prometheus, InfluxDB) may store timestamps in local time rather than UTC. Verify the data source’s time zone setting or ensure timestamps are returned in UTC.
  3. Grafana Configuration: Double-check the Grafana server settings and dashboard configurations to ensure UTC is set for time handling.
  4. Dashboard/Panel Level Timezone: Each dashboard or panel might have its own timezone settings, which could override the global configuration. Ensure consistency across all panels.
1 Like
3

In Grafana settings I have already configured the axis and the time range in UTC format, because the datasource is in UTC format. This is the insert command that I use for the database:

f"INSERT INTO energy_consumption (updated_at, idDevice,  macAddress, sensorData) VALUES (FROM_UNIXTIME({timestamp}), '{device}','{macAddress}', {data})"
4

UTC format and actual UTC time zone are two different things

The datetime needs to be UTC time zone

Check out convert_tz function to set the data to UTC time zone

convert_tz(timestamp,'US/Eastern','UTC')

so

convert_tz(timestamp,'Your Time Zone','UTC')

5

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;
1 Like