Graph time x axis + 2 hours

1

hello,

I’m new with Grafana and for my first graph I have a problem : the datetimes of data source are correct but in the graph all are + 2 hours and thus didn’t displayed correctly. Browser and source are correct.
Any idea ?
Thanks,
marc,

2

Assuming that your timezone is 2 hours from GMT that almost certainly means the timestamps in the database are in local time instead of UTC. Timestamps must be in UTC for grafana, it will adjust to local time based on the timezone in the browser.

3

Actually I should have asked a question before saying that. When you bring up a chart showing, for example, the last three hours is the timestamp written under the chart at the right hand side the correct local time or is ithe current time in GMT, or something else. For example the local time now here is 21:05 and this is what I see. Don’t worry about the data, just what is written under the chart.
image

4

hello,
In my case, x axis is correct and correspond to time in the database.The y value refer to data 2 hours before.
I don’nt understand what to change.

thanks for your support
marc,

5

In that case it is most likely that the timestamps in the database has been recorded in local time instead of in UTC. You need to look at how the data is being given to the database.

6

in the database, it’s local time not UTC

7

That is the problem. Timestamps in databases must always be in UTC.

8

Nothing found to have a good solution. I think I will stop to try Grafana.

9

The good solution is to write the timestamps to the database in UTC.
If you don’t do that what are you going to show on the graph when you scroll back to the morning of DST change when the clocks went back and look at the time in the morning during the overlap, when you would have two sets of data for the same time?

10

I have this config in mysql db for the time values

image
image1014×44 2.77 KB

11

That will put the UTC time in the database (assuming the time and timezone are correct in the mysql server. Can you check that is the case if you have not already done so.
Also show us the query you are using in grafana please.

[Edit] Also confirm that when you write data you are not giving it a timestamp but are allowing the database to fill it in automatically.

12

Mydb is a Maria Db on a synology. Time zone ok
Last records

image
image778×92 12.4 KB

Sql request :
SELECT
reading_time as time,
value1 as value

FROM SensorData

13

Are you providing a time with the samples or letting the database add the time?

14

data base add the time

image
image1003×215 17.6 KB

15

I don’t use timestamp columns I use datatime. Looking at the docs for timestamp it does store in UTC but converts to local time when it is queried. I don’t use mysql much so I don’t know what you should do about that. It may be worth trying

SELECT
  UNIX_TIMESTAMP(reading_time) as time_sec,
  value1 as value
 FROM SensorData
WHERE $__timeFilter(reading_time)
ORDER BY reading_time ASC

or

SELECT
  $__timeGroupAlias(reading_time, $__interval),
  value1 as value
 FROM SensorData
WHERE $__timeFilter(reading_time)
ORDER BY reading_time ASC

but maybe someone with direct experience of using timestamp column in mysql will help.

16

with your first code, graph display correct utc time and value corresponding but it’s not our current time

17

hello,
it’s ok now with this config

  • preferences : local browser time

  • sql request

    SELECT
    unix_timestamp(reading_time) as time_sec,
    value1 as value
    FROM SensorData

Thanks a lot for your help, you save my life :wink:
marc,

1 Like