MySQL null values are using the last Non null value

I have a table in MySQL DB with the below data

I am trying to build chart in grafana with Y-axis as response_time and x-axis as time series and below is what I have in Query Builder.

My graph panel is as below.

You can see that, when there are no values it’s not showing any dots in the graph but the line stays at 2 instead of being at 0 as there is no data .

Can you please assist.

Hi @sjabiulla, I think what you want is change the Null value settings inside Visualisation tab:

This controls how are nulls interpreted, whether to show them as 0, not show them or connect the existing values around them.

I already have this set to Null and it looks the same.

I have tried null as zero but still same issue

I think there aren’t actual explicit null values. The setting works if you have:
01-01-2019T01:01:00Z 1
01-01-2019T01:01:00Z null
01-01-2019T01:01:00Z 2
In this case it would tell the visualisation how to interpret the middle null value.

I assume in your case though you do not have actual null value, just a big gap between 2 values? If it is possible can you share the response from query inspector?

Hi @aocenas

I have copied full query inspector on the below link. Please have a link.

@sjabiulla Hey sorry for late reply. Looking at the data, and though I assume they are not the same as in the graph (seems like the timestamps are different) you can see that the request is:
“from”: “1557945000000”,
“to”: “1558031399999”,

But the first and last timestamp in the data is 1546647006000 and 1564985403000 which is outside of the shown interval. This so that the graph can look like a cut out of a bigger graph instead of ending abruptly even though there is data right behind the timeframe. This then visually creates what you see on your graph: a long line connecting last datapoint you see in graph with another datapoint that is outside of the range and so outside of the visible graph area.

You could solve this by manually adding empty (null) data points in your DB (or in the query) in some periodic interval, if you really want to show that the measurement is empty there.

do you know how to add null data points when query?what’s the sql should be use?

When querying is not possible but you can create a trigger in SQL when inserting a row in a specific table :slight_smile:

A bit late to the party but this is the solution:

SELECT $__timeGroupAlias(created_at, '24h', 0), something AS 'metric' FROM ...

So the solution is that timeGroupAlias function that will populate the missing dates in the result with whatever you put at the end. In this case with 0 values.

I hope it helps

While I am using this for MSSQL I am getting an error, Incorrect Syntax, Could you please explain it futher?