Data outside time range Europe/Madrid (CEST, +0200)

Grafana Time Series Panel
1

Server running Europe/Madrid (CEST, +0200), Grafana Browser set Time Spain, CEST UTC+02:00

  • What Grafana version and what operating system are you using?

  • Grafana v11.2.2 Debian ARM64

  • What are you trying to achieve?

  • Data in real time

  • How are you trying to achieve it?

  • Time series chart with mysql query

  • What happened?

  • Data outside time range, shows 2h extra over values of timestamp in ‘time’ column.

  • Ex. Timestamp 11:00, data in chart is at 13:00.

  • What did you expect to happen?

  • Real time chart display

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

Save your data into DB in the UTC timezone.

1 Like
3

I just tried, but same results, local time 14:25 (UTC+2) but Grafana still showing 16:25, i really can’t understand why

fdsre
fdsre1747×943 151 KB

Edit: The time column

ds
ds643×760 53.7 KB

4

Data in the DB: in UTC timezone
Grafana DB user: UTC timezone (e.g. session time zone - I guess this is your problem - it returns +2 and then Grafana UI will add it as well +2)
Grafana UI: “moves” received UTC data to browser/dashboard/panel timezone

For your simplicity: set everything on the server side to UTC.

5

how did you do that?

6

changing the timezone in mysql? by editing /etc/mysql/mariadb.conf.d/50-server.cnf

ddre
ddre1018×425 47.3 KB

7

I don’t think that changes your data in the database though. Use this function instead
convert_tz(time_stamp,'US/Eastern','UTC')

of course using your time zone name instead of US/Eastern

1 Like
8

I don’t know. Check your db doc.

9

Unfortunately this doesn’t work in my case, i believe it’s because my timestamp value has name ‘time’.

ddture
ddture2159×956 83.3 KB

10

  1. What data type is your time column?

  2. you need to modify the Format to Time series

image
image724×138 33.3 KB

image

11

I finally managed to get it working based on your solution, modified TZ with numerical values for UTC (+00:00) and -2:00 for my timezone and worked.

Screenshot 2024-10-10 at 17-43-54 Edit panel - trt - Dashboards - Grafana
Screenshot 2024-10-10 at 17-43-54 Edit panel - trt - Dashboards - Grafana1184×920 54.5 KB

But i must say this isn’t practical at all when having to add much data…seen this issue is been present since 2021 and yet can’t be fixed?¿
Having to either set server to UTC or use CONVERT_TZ doesn’t seem normal to me.
Screenshot 2024-10-10 at 17-46-34 Grafana converts UTC time data twice when Grafana server timezone is not set to UTC · Issue #30918 · grafana_grafana
Screenshot 2024-10-10 at 17-46-34 Grafana converts UTC time data twice when Grafana server timezone is not set to UTC · Issue #30918 · grafana_grafana942×312 19.8 KB

12

That’s not solution. It’s only a workaround. Are you going to edit all your queries when CEST will change to CET and vice versa? That will happen in a few days, so prepare for another problem. Strictly use UTC and let client only (e.g. Grafana) to handle local timezones.

2 Likes
13

there are tons of time zones on planet earth (not sure about other planets, never been there) instead of trying to account for every single type of time zone, a good tool would design things to use one common time zone: UTC.

1 Like
14

That’s exactly my point, no one uses UTC only. As my (and everyone else) server and db handle automatically cet/cest, why can’t grafana simply read that data as indicated in configuration of time series → Axis ->Time zone and just display it correctly based on settings of the Browser time settings picker?

16

No, nobody shows it in UTC, but everyone saves it in UTC.

Why no Prometheus user has a problem with timezone? Because Prometheus always keep data in UTC timezone by design. Poor designated SQL DBs have this problem usually. You keep in your poor DB that we will meet at 2024-10-10 09:00:00, so you will send me invitation with time 2024-10-10 09:00:00. And I guarantee that we don’t meet, because we are in different timezone, so we have time “2024-10-10 09:00:00” at different time points.
Yes, you can use local timezone, but it will be more complicated (e.g. PostgreSQL has timestamptz, so also timezone is saved - I can save in local timezone, but then I can query easily in UTC), so for your simplicity use UTC.

Grafana has proper timezone handling, but save and query in yhe UTC timezone. Yes, complain that’s wrong, but then think how can you solve problem with Daylight saving time - are you aware that different countries may have different time when they change DST, you can expect that some countries may refuse to use DST in the future at all.

So saves and query data in the Grafana in the UTC (visualize it in whatever timezone you want then in the UI) or suffer in the timezone hell forever.

1 Like