Chart not displaying results if I use the number 0 but will if I use number 0.0 ... possible bug ?

[questuk]

The problem is I use Grafana for my chart and if the very 1st number of row 1 is a 0 .... I get no graph. Although data is there !

If I manually change the database first row number to 0.0 from 0 the graph works perfectly.

It seems that grafana needs to see the number with a decimal to set up the graph ?

My SQLite JSON database setup

• OS: [e.g. KUbuntu 21.04]
• Run via docker? [e.g. no]
• Browser [Brave]
• Grafana Version 9.1.1
• Plugin Version 3.1.0
• Plugin installed via grafana online

Thanks

[questuk]

If you need any more help. please let me know

Thanks

[fr-ser]

What would help most would be a reproducible CTE. If that is not possible, then a sample database and query could also help.

RIght now it is very hard to debug the issue without any data or query.

[questuk]

Hi Sergej,

Thanks for getting back to me, let me first state I am very new to using SQLIte with JSON and also Grafana, so I may be doing something wrong ?

I have added my working database and if you can look at the image you will see my comments. I used DB Browser to setup the Table and to do any editing of the data.

Grafana & SQLIte JSON & number zero problem.zip

Thanks Gaz

[fr-ser]

When I use your query everything looks fine to me (regardless of the first value). In the screenshot in your first message I also see your time range is different. Are you sure that your issue is not simply one of filtering for the right time frame?

If your issue persists, could you maybe export the dashbaord from Grafana (that would include your visualization settings).

[questuk]

Hi,

The time range is different as they were taken at different times, but used to show you an example.

Not sure which one you need, so I exported 'Local' & 'For sharing externally' files.

I agree when you use the data in DB Browser etc and use the query everything is shown correctly. But when the same data and query are used in Grafana the 0 problem arises.

This is the chart you need to look at on my dashboard, as it is referenced to IP15 in the SQLite database.

Exported from Grafana.zip

Thanks for your time Sergej

Gaz

[fr-ser]

I opened this chart and removed the " >= 0 " from the query. While the chart obviously changes due to this it does not become a flat line. How can I reproduce your issue?

Also for time series visualization ordering by time is crucial and should be done in every query.

[questuk]

Hi Sergej,

If I remove the " >= 0 " from the query this is what my graph looks like when the database has 0.0 as the first row of IP 15.

This is the reason I want to show 0 because at 00:00 ( midnight ) the gas meter is set to 0. As seen on the video below

Also for time series visualization ordering by time is crucial and should be done in every query. I think I have done this correctly ?

Here is a video i made a few weeks ago showing the problem ...

https://user-images.githubusercontent.com/1677382/189538113-defc599e-76ef-4dd8-acb0-6e08ded5a992.mp4

I appreciate your help so far

Thanks Gaz

[fr-ser]

Also for time series visualization ordering by time is crucial and should be done in every query. I think I have done this correctly ?

The image you showed selected the time but you are missing an "order by time ascending" clause, which is integral for a valid time series.

Could you let me know if you still see the same issues after also sorting by time? Because right now it looks to be a Grafana chart scaling "problem", which of course heavily depends on the shown values, which might be out of order.

[questuk]

Hi Sergej,

Would it be possible to change my query for me and post it on here ? As I am very new to SQLIte and JSOn, if you can do that then I will test it.

Thanks

Gaz

[questuk]

Hi

I finally have had some time to come back to this, as I have been very busy lately.

This is my new query, which seems correct referencing your previous comment.

select  json_extract ( DATA,'$.meter_reading' )  as Meter,
        json_extract ( DATA,'$.iso')  as time
from    Energy
where   DEVICE = 'IP15'
ORDER BY time ASC ; 

The chart still does not work if the 1st line is a 0 in my database.

Any ideas ?

Thanks

Gaz

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[questuk]

Hi,

Did you ever find a solution to this ?

Thank you Gaz

[fr-ser]

Not really. If you could provide a sample query with the data in a CTE or subquery to reproduce the issue that would be most helpful. But I'll try to take another look eventually.

[questuk]

Hi,

I have added a zip file with a video & database & query & Grafana export dashboard, hope this explains the problem.

Thanks Gaz

TEST - zero problem - 31-10-22.zip

[fr-ser]

Can you try to pin the problem down to a simpler query? A CTE looks like this

with cte(timestamp, sensor_name, value)
as (VALUES ('2022-10-30T01:50:00.000+01:00', 'Inlet Pipe', 1),
           ('2022-10-30T01:59:00.000+01:00', 'Inlet Pipe', 5),
           ('2022-10-30T01:10:00.000+00:00', 'Inlet Pipe', 10),
           ('2022-10-30T01:20:00.000+00:00', 'Barrel Lid', 15))
select unixepoch(timestamp) AS ts, value from cte
order by 1 asc

Could you provide me such a query (not in a zip file but just here as text) that causes the issue?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.