(SOLVED!) Time trouble (2 hours offset)

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 ?

Check this out for MSSQL

Thanks but I have kept read only permission to DB. Create function permission is denied. I want timefilter() to return local time or one which I select from dashboard’s time zone, but it’s not possible I see. It only returns UTC. My DB stores timestamp based on local time zone.

You can see this:

I am having this same issue. Data in my MySQL database is in UTC timezone in datetime data type format (YYYY-MM-DD HH:MM:SS):

image

The server is in PST(UTC-8:00):

image

I have many queries that this is affecting. Previously Grafana was working as intended, but it seems to randomly decide to stop interpreting these as UTC and I am left with an offset of 8 hours. See below, the graphs are updating in real time and should only contain entries up to 10:00AM, however it shows as reading the datetimes as they are in UTC format, seemingly without doing any conversion. Note that in this screenshot, the time in Grafana is set to Browser (UTC-8:00). I do not understand why Grafana is no longer subtracting 8 hours from the time it is reading from the database. It should be interpreting the datetime as UTC, and subtracting 8 hours.

Here is one of my queries. $time_column is a custom variable I have set to equal the field seen above, time_start:

SELECT
  $__timeGroupAlias($time_column, $__interval, 0),
  count(*) as value,
  'Outbound' as metric
FROM $log_table
WHERE
  $__timeFilter($time_column)
  AND
  direction = 'OUT'
GROUP BY 1
ORDER BY $__timeGroup($time_column, $__interval, 0)

Using UNIX_TIMESTAMP() on my time column here results in the following error:
macro __timeGroup needs time column and interval

And does not resolve the issue.

The weirdest part is that has been working fine up until recently, the Grafana has been running in a production environment for about a week and was displaying correct values. No changes were made to the system, MySQL, Grafana server, or otherwise, yet this problem seems to appear out of nowhere.

Solved: