AWS timestram query - avoid gaps in data to be connected

[emanuelherrmann]

Hi

At our company we have built lots of graphs in Grafana that are based on AWS Cloudwatch metrics queries. Now, we are migrating all our metrics into AWS Timestream and so we are migrating all our Grafana dashboards too.

Most of our panels need to display an aggregated value of some metric for every minute.

We have faced the following issue when the metric does not have a value for a given minute. Grafana will join the gap with a line, instead of just showing the gap.
There is no data for 9:54 & 9:55 in the graph query below.

We have tried setting the Null value option to both null and null as zero, but it doesn’t make any effect.

With CW metrics, the queries seem to return a row with a null value for at least one of the missing timestamps and that seems to be sufficient for Grafana to display the gap

We have tried several different ways to get a null value in the Timestream query result but we haven’t succeeded mainly we.

• Used interpolate_fill function to fill the missing data, but we can only fill with a number not null. and 0.0 is not an option for us.

• Use Create_time_series, but also does not accept nulls

Here’s the timestream query for reference
WITH binned_timeseries AS (
SELECT BIN(time,1m) as binned_timestamp, max(measure_value::double) as val, log_source as series
FROM $__database.$__table
WHERE measure_name = ‘head_block’
AND serial_number = ‘${SerialNumber}’
AND $__timeFilter

GROUP BY 1, 3
ORDER BY 1, 3 ASC

)

SELECT series, CREATE_TIME_SERIES(binned_timestamp,val) as “Head Block”
from binned_timeseries
group by series

As having null values in the queries does not seem to be possible with AWS Timestream (happy to be corrected here ). Is there a way to make Grafana display a gap when there are missing rows ?

I appreciate any help or hints here

Thanks in advance

Emanuel

[matthewhuck]

Instead of filling with 0.0, use sqrt(-1), it will become NaN and unchartable…

[emanuelherrmann]

Thanks @matthewhuck that work around the problem and Grafana displays the gap in the chart