2h of data (on the left) gets cutoff

Grafana Time Series Panel
1

Hi everyone,

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

grafik
grafik1546×693 86.8 KB

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:

grafik
grafik1893×677 73 KB

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:
https://localhost:3000/t/first-part-of-graph-always-missing/32581

Maybe something in that direction, but the time is the same on the server and my PC (both are synced over the internet)
https://localhost:3000/t/graph-data-points-cut-on-left-in-all-graphs/16293/8

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

2

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

grafik
grafik986×339 15.7 KB

3

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):
    image
    image1250×966 81 KB
  • What type does your Datum column have? Timestamp or datetime?
4

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?

5

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?

6

Try it:

select unix_timestamp(Datum) as "time"
...
1 Like
7

Hi, I had the same issue. In my case it seems that the _timefilter-Makro does not reflect the local timezone. See this post:
https://localhost:3000/t/timefilter-does-not-produce-the-correct-time-range-in-sql/20841/6?u=bmerz

1 Like
8

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