(SOLVED!) Time trouble (2 hours offset)

I can’t get my data to display in the right time. I’m not sure why, but I get a 2hour gap in the start of the graph, while the data is available in MySql:

What also is very strange is that 2 graphs with exactly the same structure have different time offsets. As you can see the pressure graph latest datapoint is 10:55, while the solar latest datapoint is 12:25. In MySQL the times are exactly the same:

Query for the pressure graph:
SELECT datetime AS "time", pressure FROM weather_stats WHERE $__timeFilter(datetime) ORDER BY datetime

Query for the Solar graph:
SELECT datetime AS "time", solar FROM solar WHERE $__timeFilter(datetime) ORDER BY datetime

The pressure (upper graph) is stored in MySQL like this:

And the Solarpanel information is stored like this:
34

As you can see, the date & time are exactly the same, but in the graph the solar panels are 2 hours ahead!

I’ve changed the timezone in grafana several times (Defaul/local browser/UTC), but that doesn’t change anything.
The time on the grafana server (MySQL is also running on that server):
Mon Jun 17 11:02:20 CEST 2019

The time on my laptop (browser time):
ma 17 jun 2019 11:02:20 CEST (Dutch notation)

I’m using Grafana v6.2.2

I just can’t figure out what is going on. Can anyone help me?
Thanks!

Are they in the same database?
Using the mysql command line run
select * from weather_stats order by datetime desc limit 1
and
select * from solar order by datetime desc limit 1
which will get you the latest samples from each one and check the timestamps.

They are in the same database:
32

The results from the queries:
select * from weather_stats order by datetime desc limit 1
125837 2019-06-17 11:50:00 22.40 24.00 57 1020 771 0 3.700

select * from solar order by datetime desc limit 1
17 2019-06-17 11:50:00 1770

If I visualize the data as a table, you can see that the time for solar is 2 hours ahead in grafana compared to the MySQL database:

Very odd, what happens if you put them both on the same graph?

I’ve added the Solar data to the Pressure panel, and displayed it as a table:

It seems that all times are displayed 2 hours ahead of the database

1 Like

I have no idea what might be causing it. Sorry.

Thanks a lot for trying!:+1:

Sounds like one field is a DATETIME, and one is a TIMESTAMP. From mysql manual " MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME .)"

They are both DATETIME:

23 02

I a not sure what the original pictures are showing. Does the time shown in the popup match the timestamp on the axis?

The time on the X-axis represents the current time, but the datapoints are 2 hours ahead in the future, and are off the chart.
17

So the X-axis goed up to 15:00, the datapoints also go up to 15:00 in the database, but Grafana plots them 2 hours ahead, at 17:00

The datapoint that is in the tooltip (1.967kw at 15:00:00) is actually the datapoint from 13:00:00:
image

SOLVED!

I’ve changed datetime to UNIX_TIMESTAMP(datetime) in the query, and everything is back to normal!

Thanks everyone for trying to help me!

Did you have to do that on both of them?

Yes, I did it on both of them, and now everything is perfect!

CONVERT(VARCHAR(20), YourVariableName , 100) as time

Please refer this for timeformats in mssql

There is issue in grafana where it checks time as UTC time but when in interval comparison or group by it checks time as local and only converting interval time to UNIX .

Solution - You should add one more column to table which should contains UNIX equivalent time of your timestamp and pass that in time column and everything will work.

Eg.

Local Browser Time - 2020-05-31 17:21:05 (It is UTC + 5:30)
UNIX_TIMESTAMP - 1590945665

Can we have sample working query ?

I’m having the same issue where where I have a timeStamp column which is in fact in DATETIME format with the format “YYYY-MM-DD HH:MM:SS”
But changing the request like you did didn’t work for me. I’m in UTC-5:00, and when I choose UTC0, the time on the data is good , but obviously, I’m 5 hours into the future.

SO even though My timeStamp on the DB is 12:00:00, when I open the data on grafana it shows 7:00:00

SELECT
    timeStamp AS "time",
    pd1DigitalNumberMean
FROM DetectorUnitsData
WHERE
    UNIX_TIMESTAMP(timeStamp) AND
    unitID = '8'
ORDER BY timeStamp

Oh, I just made it work. Here is my working query:

 SELECT
      UNIX_TIMESTAMP(timeStamp) AS "time",
      Data
 FROM tableName
 WHERE
      $__timeFilter(timeStamp)
 ORDER BY timeStamp
1 Like

By the way I have same issue but with MS SQL not MySQL, and UNIX_TIMESTAMP doesn’t work, saying it’s not a built-in function. Any help on that ?