Show us the left axis on the bottom and and what the bottom one looks like if you remove the first record.
Also is it the first record in the database that is important, or the first record in the grafana window?
My actual dashboard is running live and adding other data constantly.
So i did what you asked on the test bed one.
Is that showing the same data but the seond one has an extra record on the front with 0.0 in it?
What do you get if you force the y axis to the range 0 to 1?
yes same data, just changed the first database row to 0.0 from 0
I will have to look to see how to change the y axis !
It is in the dashboard settings. I am wondering if grafana is not calculating the axis correctly, so the data points are there, but they are all squashed at the bottom so you can't see them.
This is the Table of the data and they are all 0
If I do the trick of adding 0.00 to database I see 0.00 and also the actual correct numbers.
I scrolled down to get to the numbers in this image.
Do you mean that adding a record on the front but changing nothing else in the the database, so not rewriting records, just adding one record on the front, changes what the query returns for the unchanged records?
yes that is correct
well actually i find the first row and change that from 0 to 0.0 and then save to database.
Can you show us exactly what is in the database? Not using JSON extract?
you will look in Energy and then look for IP15
I will be back on my PC in the morning 
Thanks
Well I can't see anything wrong with that. In JSON there is no difference between numbers 0 and 0.0, as far as I know. It looks like a bug in the grafana db driver or in grafana itself to me.
Perhaps there is a better work around rather than making it 0.001. Add a debug node showing what is going into the db write in node-red and how you have configured the db node.
You say you don't know a lot about sqlite. Is there a reason you are using that rather than, for example, influxdb which is designed specifically for time series data?
This is the function node that does all preparation before sending to SQLite.
[{"id":"de5fe3eb7fc167dc","type":"function","z":"675ad771.04cc68","g":"489231f22dbc625b","name":"Data -->","func":"\nlet device = \"IP15\"; // ENTER HERE ... the IP address.\nlet location = \"Garage - Gas\"; // ENTER HERE ... Location\n\nlet new_date = new Date(); // Does NOT change with time zones across the world.\nlet iso = new Date().toISOString(); // RFC 3339 format ... for Grafana etc.\nlet timestamp = Math.round(new_date.getTime() / 1000); // getTime = milliseconds ... this removes last 3 digits to change to seconds ... for Grafana etc.\nlet meter_reading = msg.payload; // Store the latest meter reading.\nlet total_cost = 0; // Total cost at the end of the day. This makes Grafana show 0 until 23:59.\nlet kWh_cost = 0; // Declare the variable.\nlet kWh_from_m3 = 0; // Declare the variable.\n\n // CONVERT from m3 (cubic metres) to cost of kWh\nmsg.payload = msg.payload * 1.02264; // Multiply by volume correction factor.\nmsg.payload = msg.payload * 39.7; // Multiply by calorific value.\nmsg.payload = msg.payload / 3.6; // Divide by kWh conversion factor.\n\nkWh_from_m3 = msg.payload; // Store converted so I can compare energy used of Gas & Electric in graph etc.\nkWh_from_m3 = parseFloat(kWh_from_m3.toFixed(2)); // Above creates 5 decimal places, this limits to 2 decimal places and keeps it as a number.\n\nmsg.payload = msg.payload * 0.0632; // ENTER HERE ... Supplier Price per kWh. \nmsg.payload = msg.payload + 0.2486; // ENTER HERE ... Standing charge per day.\nmsg.payload = msg.payload * 1.05; // Adds 5% VAT\n // End of conversion\n\nkWh_cost = msg.payload; // The Total cost including all charges.\nkWh_cost = parseFloat(kWh_cost.toFixed(2)); // Above creates 5 decimal places, this limits to 2 decimal places and keeps it as a number.\n\n \nif (msg.store_total_cost === true) // Will only update at precise time (23:59) set in the node before this.\n {\n total_cost = kWh_cost\n msg.store_total_cost = false\n } \n \n\nlet payload =\n { \n cost: kWh_cost, // ENTER HERE ... any new items to add to database, in this section.\n iso: iso,\n location: location, \n meter_reading: meter_reading,\n timestamp: timestamp,\n total_cost: total_cost\n };\n\npayload = JSON.stringify(payload); // Converts a JavaScript value to a JSON string\n\n\nData_sequence = \"( device,data) values(\"; // DEVICE and DATA are fields in the database.\nData_sequence = Data_sequence +\"\\'\"+ device +\"\\',\" +\"\\'\"+ payload +\"\\'\"; // This is the actual data.\nData_sequence = Data_sequence +\")\";\n\nvar topic = \"INSERT INTO Energy \" + Data_sequence; // The insert string to send to database.\n\nmsg.topic = topic;\nmsg.payload = \"\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":820,"y":410,"wires":[["0c586c97883b133b","0c0f0829ba5ba239"]]}]
and now I really am going to sleep ....
When you are awake again could you add a debug node showing what is going into the sqlite node please. Set the debug node to Output complete message.
I note you are missing a let from the front of the first use of Data_sequence bit that won't change anything.
Hi Colin,
The hot water is being heated at the moment so it does show a value, i can clear that value to 0 if you want ?
INSERT INTO Energy ( device,data) values('IP15','{"cost":0.37,"iso":"2022-09-04T08:09:10.625Z","location":"Garage - Gas","meter_reading":0.14,"timestamp":1662278951,"total_cost":0}')
If you add a Change node configured like this after the function node it will change any 0 meter reading values to 0.0, though I am not certain that will make any difference to Grafana. I don't believe that it should make a difference, Grafana should be perfectly happy with 0. Check in the debug that it looks correct.
1 Like
Hi Colin,
Took me a while to sort and change things, but looking at my database it does show 0.0 
So that seems to be the solution, I will upload and try it with Grafana soon.
I have also asked the creator of the Grafana SQLite JSON plugin, if he can check the problem I am having here.
Thanks for your help 
Hi,
Just tried it on Grafana and it does work.
Thanks
1 Like
Excellent.
Just a note about the code in the function. Looking at the lines
let Data_sequence = "( device,data) values(";
Data_sequence = Data_sequence +"\'"+ device +"\'," +"\'"+ payload +"\'";
Data_sequence = Data_sequence +")";
var topic = "INSERT INTO Energy " + Data_sequence;
Code like that can be a easier to write and understand using Template Literals, so that becomes something like
const Data_sequence = `( device,data) values('${device}','${payload}')`
const topic = `INSERT INTO Energy ${Data_sequence}`
Hi,
Thanks for that info, i made my function from Steve Cope and adapted it to my needs, he inspired me to use SQLIte as JSON.
Without his tutorial I would never have got this far !
For someone like me who dabbles with Node-RED .... SQLIte is easier to learn and use.
Now that you have solved this problem
Thanks Colin you have been a great help.
Hi Colin, now that I have this working ... in the next few months I will look at influxdb.
Do you use a particular GUI to look at the data ?
