2h of data (on the left) gets cutoff

[chaftaliemusic]

Hi everyone,

I am just trying out Grafana right now and on every line graph I make the first two hours are missing:

The Data gets loaded, the generated SQL works and in the Query Inspector I can see the missing Data:
While writting this the behavior changed a little (or whats more likely, I just overlooked something…).
It generates the right SQL Query with the right timestamps, but I do not get the right Data back:

The first data row has the unix time 1594404025000 which converts to 10.7.2020, 20:00:25. Thats the first Datapoint shown in the graph.

When I execute the SQL query in heidisql I get the data which it should select:
[As I am a new user I can’t upload more than two images in one post. See first reply)

HeidiSQL and Grafana are running on the same PC (Win10), the mysql db is running on an external server(WinServer2019 I think). Both machines have the same time.

I ve already read through these two posts:

Does not seem to be the same issue:

First part of graph always missing

Hi everyone, I’m sure this is an easy one for you guys but I’m tearing my hair out trying to figure out why ALL my graphs are missing the first section (see screenshot), regardless of which time window I select, though it does get worse the more granular I go (see below). Once I get to last 1 hour I’ve lost a lot of info and anything under there it does not display any info at all. These are from InfluxDB so I’m guessing something is wrong with my query somewhere? The data is definitely presen…

Maybe something in that direction, but the time is the same on the server and my PC (both are synced over the internet)

Graph data points cut on left in all graphs

The time in the database should be in UTC, so the time zone of the server should be immaterial. It is difficult to see what else the browser code can do other than assume that the clock on the PC is correct for the user, so when you ask for data for, say, 10:00 it works out what that is in UTC and gets the data for that timestamp from the DB. What else could it do?

Maybe I am just overseeing something obvious…
Does someone know how to fix that?

[chaftaliemusic]

Screenshot of heidisql with the correct Data (Same SQL query as Grafana uses):

[daniellee]

This looks time zone related. It’s two hours off and you seem to be located in Germany (which would be UTC+2).

Starting with the simple stuff:

• What version of Grafana are you on? (Looks like 7.0?)
• What are your time zone settings for the dashboard (in dashboard settings):
  
  
• What type does your Datum column have? Timestamp or datetime?

[chaftaliemusic]

Yep, I am in the same timezone as Germany (I am from Austria).

• I am on Grafana v7.0.5 (efbcbb838b), just installed it a few days ago.
• My Timezone was set to Default, changing it to Vienna did not change anything. (Changing it to London UTC+0 did not change anything)
• datetime

When testing with heidiSQL I just copied the SQL query directly from the query inspector and with that there should not be any problems with timezones? because unix time is unix time isn’t it?

[chaftaliemusic]

I ve just tried to move the data from the original mysql 8.0.2 DB to an 10.1.38-MariaDB.
Using my MariaDB, which ist installed on the same PC as Grafana, does not solve the problem.

Any ideas where the problem might be?

[sancho1234]

Try it:

select unix_timestamp(Datum) as "time"
...

[bmerz]

Hi, I had the same issue. In my case it seems that the _timefilter-Makro does not reflect the local timezone. See this post:

__timeFilter does not produce the correct time range in SQL

I have the same issue with Grafana: Server, sensors and browser are in German timezone datatype in MySQL is DATETIME Time is logged in UTC by sensors As expected the actual time is converted correct to the local timezone When using macro “$__timeFilter(column)” data is missing To fix the issue I use this filter: timeUTC BETWEEN convert_TZ($__timeFrom(),@@SESSION.time_zone,'+00:00') and convert_TZ($__timeTo(),@@SESSION.time_zone,'+00:00') In my opinion the timezone conversion is missing in t…

[chaftaliemusic]

I ve tried both solutions, and both worked!

Original SQL Code:

SELECT Datum AS “time” FROM …

First solution:

SELECT unix_timestamp(Datum) AS “time” FROM …

Second solution:

SELECT convert_TZ( Datum ,@@SESSION.time_zone,‘+00:00’) AS “time” FROM …

Thanks for the help and the working exmaples! @sancho1234 and @bmerz