I already drafted this post when I stumbled across the solution. I’ll post it anyway in case it helps someone!
Original post:
For a while no I’ve had an issue where one of my graphs started to get cut off to the right for no obvious reason. Now I see the same behavior in some new graphs I’ve added and for sime time windows I can hardly see anything.
First my setup: I’m using Grafana 8.4.4 as a Docker container in unraid. It’s reading MQTT messages from Postgres, written there by my own message recording software, which is adding the timestamps. Most of what I use are simple time series charts using selected MQTT topics.
As an example I’ll show my humidity graphs (working fine) and my CO2 graph (cut off) with various time windows. Both receive data every couple of seconds and I’ve checked the database to verify that the data is there. Except for one humidity value (thermostat), all graphs should go up to around a minute (or less) of the present based on the message frequency and my update frequency (10s).
Here is my query for humidity:
SELECT
"ReceivedOn" AS "time",
"Topic" AS metric,
CAST("Payload" AS DOUBLE PRECISION) AS value
FROM "MqttRecorder_Schema"."Messages"
WHERE
$__timeFilter("ReceivedOn") AND
"Topic" LIKE '%/humidity'
ORDER BY time
And here is my query for CO2:
SELECT
"ReceivedOn" AS "time",
"Topic" AS metric,
CAST("Payload" AS DOUBLE PRECISION) AS value
FROM "MqttRecorder_Schema"."Messages"
WHERE
$__timeFilter("ReceivedOn") AND
"Topic" LIKE '%/co2'
ORDER BY time
Here are different time windows for both time series. Humidity is always as expected (disregard the thermostat graph, it’s very low-frequency):
As you can see, values beyond the cut-off times exist, but are different depending on the time window.
I can even get the full graph when selecting “Last 30 minutes”.
I’ve tried setting the time zone explicitly, but that didn’t change anything.
Solution:
Two problems:
- Casting MQTT payloads while using “LIKE” on the topic has some pitfalls
- Grafana doesn’t directly tell you about failed queries.
I looked through my Postgres logs and discovered some failed queries.
In the co2 case, I had an alarm that would notify me of high co2, but the MQTT topic for it also ended in “co2”. The payload for that was a string, couldn’t be cast to double, so the Grafana query would just fail silently, and that part of the graph was then missing. The solution was to make the LIKE statement more accurate.
I had another graph using “Topic” LIKE ‘%_POWER’ since all the topics ended in “_POWER”. So I got errors like this:
invalid input syntax for type double precision: “ON”
Turns out I had other topics ending in “POWER”, that would be “ON” or “OFF”. The underscore seems to have some special meaning in SQL that I wasn’t aware of. So I escaped it and it worked:
“Topic” LIKE ‘%\_POWER’