Data outside time range but everything looks oke

Grafana Time Series Panel
1

Hello all,
I have a very strange problem with Grafana
I have a MySQL database containing values from my smart electricity meter to monitoring my energy usage
The values are stored in the TIMESTAMP column.

Screenshot0

I’m living in the Netherlands so currently we have UTC+2 because of daylightsaving
You can see in the image below that Grafana using UTC+2 because it recalculates the time because the little popup also says 11:01:06 and matches the time scale of the graph.

When I for example put the timezone to UTC then the popup below says 9:01:06 and the timescale below it stays on 11:00

Screenshot1
Screenshot1793×308 21.2 KB

Now comes the strange part. When I’m selecting for example the time from 11:00 to 11:30 I get a “Data outside time range” error.

Screenshot2
Screenshot2796×310 18.3 KB

Screenshot3
Screenshot3789×307 10.7 KB

I should say, if Grafana is capable of recalculate the data from UTC to UTC+2 then it should also be capable of recalculate the data from UTC+2 to UTC as present in the DB

Screenshot4

Anybody have any clue?

Thanx,

Regard,
Robbert

2

convert to utc something like this for your time zone

CONVERT_TZ(created, ‘UTC’, ‘America/New_York’)

3

Why should i do that?

4

A little more information

When i select last 1 hour i have no data,
When i select last 2 hour i have only the last hour of data

Strange because UTC and UTC+2 is 2 hours difference, not 1

5

I tried, exactly same results

6

because that is what grafana wants as it is a common time zone
imagine if it had to account for every single type of time zone

lets see your query

7

At this moment this is my query
Still not working as expected. There is somewhere an hour lost in time.

When i select last 12 hours, i only get last 11 hours
When i select last 6 hours, i only get last 4 hours
When i select last 3 hours i get no results

SELECT CONVERT_TZ(TIMESTAMP,‘UTC’,‘Europe/Amsterdam’) AS “time”,
(0-ACTUAL_BACKDELIVERY_KW) AS “teruggeleverd”,
ACTUAL_DELIVERY_KW AS “verbruikt”
FROM METER
WHERE
$__timeFilter(TIMESTAMP)
ORDER BY TIMESTAMP

8

click on Query inspector and what do you see? copy that query you see in query inspector and run it on mysql. do you see the same issue?

9

I guess you don’t save data (TIMESTAMP column) in UTC.

1 Like