TimeSeries is plotting 0 instead of 'Null' at times where there is no data

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?
    Grafana v.9.2.0, MSSQL database

  • What are you trying to achieve?
    Plotting multiple graphs (time-value with multiple labels) in a time series panel with one x-axis.

  • How are you trying to achieve it?
    panel time series, format ‘Time Series’, query from mssql SELECT dateTime as time, ChannelName as metric, value FROM … WHERE … ORDER BY dateTime asc;

  • What happened?
    While transforming the data from mssql (time, metric, value) to the matrix (frames time,value for each metric/label), grafana shouldn’t fill the gaps (time/metric with no value in the query data) with 0. These ‘cells’ should be left blank / ‘NULL’.
    image

  • What did you expect to happen?
    Time-Label-Value-Combinations with no original data left blank (NULL).
    image

I tried Transform “Prepare time series” (multi-frame time series), but it did not help.
If I request the data with grouping from mssql (SELECT …max(value) … GROUP BY dateTime, ChannelName ORDER BY …), everything is working properly. But the query result in sql is the same than to pull the data without the grouping, because every datetime/channelname-combination is unique.

=> What do I have to do to get the correct series without using Group By (performance issues) in MSSQL?

2

What datatype colum value column?

What does the query result look like when done outside of grafana ie ssms

3

time: dateTime
metric: varchar
value: float

The query looks exactly the same, with or without grouping.
image

4

But there is no gap in sql where it is filling it with 0
Do the pivoting in mssql - more control - rather than in grafana. Leave grafana to do visualization and let sql do what it is good at

See if pivot in ssms has same issue

5

No, pivoting in sql works fine, as does the transform to matrix in grafana. But I don’t understand what grafana does with the exact same data. In other words: why does it work with group by, and without it doesn’t?

6

latest version of grafana seems to work.

image
image1139×654 45.1 KB

Best way to spy on what it does is seee what sql profiler shows

Not sure if it does it locally after fetching data. Sql profiler might help. Group by does aggregation so maybe it eliminated no aggregate -able rows? Guessing

SQL Query profiler shows nothing. Conclusion, matrix happens in grafana side after fetching the data from sql, interesting.

image
image925×449 62.8 KB

7

Hi.
Got the difference. Group by changes the float-values, the precision specifically. Grafana seems to have problems with the float-value.
Once I casted the float-Values zu decimal, grafana doesn’t fill the NULLs with 0.
This worked for me:
SELECT
dateTime as time,
ChannelName,
cast(value as decimal(5,3)) as value
FROM