Hey guys,
I’m new to Grafana. I’m using MySQL as persistence and want to visualize some data with Grafana.
I started yesterday at around 4 o’clock pm. But in my charts there is a timezone missing (from 11am until 1pm).
It doesn’t matter which time range I select, there are always data points missing at the beginning.
Any idea?
Do you have values saved in MySQL for that time period?
Yes and they are displayed If I change the time range (2nd screenshot).
This are all available data in the database:
I had similar when using the ‘Discrete’ panel, to show Presence. The query contains a timestamp to retrieve data from, but as you’re recording data hourly (my presence could have been many many hours), the previous entry was not shown.
Therefore, I found I needed to amend the query to UNION the most recent data entry from before the start time, like this:
FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as value,
'Confused' as metric
FROM openhab.item0001
WHERE $__timeFilter(time)
UNION
(SELECT
FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as value,
'Confused' as metric
FROM openhab.item0001
WHERE FLOOR(UNIX_TIMESTAMP(time)) < $__timeFrom()
ORDER BY time DESC)
ORDER BY time_sec ASC
Here’s what it looks like if I remove the UNION from the first series:
FLOOR(UNIX_TIMESTAMP(time)) as time_sec,
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as value,
'Confused' as metric
FROM openhab.item0001
WHERE $__timeFilter(time)
I have only this query:
SELECT
time AS "time",
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as 'Anwesenheit'
FROM presencesebastian_474
WHERE
$__timeFilter(time)
ORDER BY time asc
But the problem also exists with the other charts.
What you’re seeing is exactly correct.
Your query is not retrieving enough data to fill in the beginning of the graph.
Try this:
SELECT
FLOOR(UNIX_TIMESTAMP(time)) AS "time",
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as 'Anwesenheit'
FROM presencesebastian_474
WHERE
$__timeFilter(time)
UNION
(SELECT
FLOOR(UNIX_TIMESTAMP(time)) as "time",
CASE WHEN value = 'ON' THEN 1 ELSE 0 END as 'Anwesenheit'
FROM presencesebastian_474
WHERE FLOOR(UNIX_TIMESTAMP(time)) < $__timeFrom()
ORDER BY time DESC)
ORDER BY time asc
This should also select the newest entry that is before the Start Time for the query as well as those which fall within the correct time period.
I think you may also be seeing a timezone difference between Grafana and MySQL timestamps…
