Generate chart from SQL

[Csongor Varga]

Hi Guys,

First of all thanks for all the help previously on SQL and other bits. I managed to put some code together which generates a chart using multiple data lines from database.

My database table looks like this. These values are stored in different frequence, some in 5 minutes, others in 15 minutes.

I did the following UI to operate the chart. On the left there is a form which contains the display period and data points. You always have to select a period and a data point. I use the value of each element in the form. E.g. period/week, period/day, period/yesterday and the data points are always in <device>/<sensor> format. Based on what I am getting back from form I generate the SQL queries for SQLite, joining the individual results and convert the data to the object format expected by the chart:

So far I have all sorts of rubbish data so the chart looks all over the place due to auto scale. But it proves the concept.

I don't really like how the form looks like, so I will most probably try something with the UI Template, hence my questions in the other topic. 

The code is here:

[{"id":"b89d8b25.670e18","type":"function","z":"15e1f6a1.aeab19","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar Week  = 604800000 ; //7 Days\nvar Day   =  86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (var property in msg.payload) {\n    if (msg.payload.hasOwnProperty(property)) {\n        //output.push({ payload: property + \" has value \"+msg.payload[property] });\n        if (msg.payload[property]) {\n            var parts = property.split(\"/\");\n            if (parts[0]===\"period\") {\n                switch (parts[1]) {\n                    case \"today\":\n                        fromdate = epoch-Day;\n                        enddate = epoch;\n                        break;\n                    case \"yesterday\":\n                        fromdate = epoch-2*Day;\n                        enddate = epoch-Day;\n                        break;\n                    case \"week\":\n                        fromdate = epoch-Week;\n                        enddate = epoch;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM sensor_data WHERE device='\"+parts[0]+\"' AND sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n            }\n        }\n    }\n}\n\noutput[output.length-1].complete=true;\n\nreturn [ output ];\n\n//msg.topic = \"SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= \" + fromdate + \" AND epoch <= \" + epoch ;\n//return msg;","outputs":1,"noerr":0,"x":297,"y":93,"wires":[["97710c81.04837"]]},{"id":"97710c81.04837","type":"sqlite","z":"15e1f6a1.aeab19","mydb":"1c25415d.b8427f","name":"DB","x":443,"y":94,"wires":[["d8204a71.a0b7b8"]]},{"id":"8770c8b5.64db38","type":"ui_chart","z":"15e1f6a1.aeab19","name":"Chart","group":"bd903d6.d9fc7c","order":0,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"%a %H:%M","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":"1","removeOlderUnit":"604800","x":790,"y":194,"wires":[[],[]]},{"id":"445b8970.f10ee8","type":"function","z":"15e1f6a1.aeab19","name":"Prep","func":"var msg2 = [];\n\n// this is the logic when there are multiple data sets are received\nfor (var i=0; i<msg.payload.length; i++) {\n    var output = [];\n    for (var j=0; j<msg.payload[i].length; j++) {\n        output.push([msg.payload[i][j].epoch, msg.payload[i][j].value]);\n    }\n    msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensor, values : output});\n    //msg2.push({ key: \"test\", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":774,"y":96,"wires":[["8770c8b5.64db38"]]},{"id":"7bd93909.e45ba8","type":"inject","z":"15e1f6a1.aeab19","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":132,"y":216,"wires":[["3dda44e3.798ecc"]]},{"id":"3dda44e3.798ecc","type":"function","z":"15e1f6a1.aeab19","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":460,"y":215,"wires":[["8770c8b5.64db38"]]},{"id":"6adefc94.74caa4","type":"ui_form","z":"15e1f6a1.aeab19","name":"Data points","label":"","group":"1e17cd0b.862863","order":0,"width":0,"height":0,"options":[{"label":"Period: today","value":"period/today","type":"checkbox","required":false},{"label":"Period: yesterday","value":"period/yesterday","type":"checkbox","required":false},{"label":"Period: this week","value":"period/week","type":"checkbox","required":false},{"label":"Miflora temperature","value":"miflora/temp","type":"checkbox","required":false},{"label":"Miflora battery level","value":"miflora/battery","type":"checkbox","required":false},{"label":"Miflora sunlight","value":"miflora/sunlight","type":"checkbox","required":false},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"checkbox","required":false},{"label":"Miflora soil fertility","value":"miflora/fertility","type":"checkbox","required":false},{"label":"Solar power","value":"growatt/power","type":"checkbox","required":false},{"label":"Solar voltage","value":"growatt/voltage","type":"checkbox","required":false}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"miflora/temp":false,"miflora/battery":false,"miflora/sunlight":false,"miflora/moisture":false,"miflora/fertility":false,"growatt/power":false,"growatt/voltage":false},"payload":"","topic":"","x":102,"y":40,"wires":[["b89d8b25.670e18"]]},{"id":"d8204a71.a0b7b8","type":"join","z":"15e1f6a1.aeab19","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":585,"y":94,"wires":[["445b8970.f10ee8"]]},{"id":"1c25415d.b8427f","type":"sqlitedb","z":"","db":"/home/pi/sqlite/nodered"},{"id":"bd903d6.d9fc7c","type":"ui_group","z":"","name":"Report","tab":"1e3fe400.0baf5c","order":2,"disp":true,"width":"18"},{"id":"1e17cd0b.862863","type":"ui_group","z":"","name":"Selection","tab":"1e3fe400.0baf5c","order":1,"disp":true,"width":"5"},{"id":"1e3fe400.0baf5c","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9}]

[Julian Knight]

Looks good!

[Jéan Roux]

Nice ! Wanted to do this for a long time, will try it, thanks !

By the way, how do you measure Soil Fertility ?

[Csongor Varga]

I am using a xiaomi miflora bluetooth soil moisture sensor. I have a video on that and the code in node red flows.

Csongor

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/FhLUZT7q6Sw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c3c043f3-f9af-4a22-80c7-f6c5dc024c69%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Zenofmud]

Remi,
it would help if you export your flow and paste it into your message so people can see what YOU have coded.
—Paul

[remi richard]

Hi,

I tried using this flow with my own database. I just changed your columns for mine. In the first function, I get the error 

 

TypeError: Cannot set property 'complete' of undefined

Any idea what the problem could be? I haven't changed anything other than column names. I only made changes to the first function node where the topic is set and pointed the sqlite node to my DB. I didn't make any other changes to the code provided by 

Csongor Varga in the original post.

 

EDIT:

Here is my database schema:

CREATE TABLE data_table (
    device_id integer NOT NULL,
    data_type TEXT NOT NULL,
    data_value REAL NOT NULL,
    data_time DATETIME NOT NULL);

Here is my flow.

[{"id":"8de8df24.2626e","type":"function","z":"d8a790c3.eeaf8","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar Week  = 604800000 ; //7 Days\nvar Day   =  86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (var property in msg.payload) {\n    if (msg.payload.hasOwnProperty(property)) {\n        //output.push({ payload: property + \" has value \"+msg.payload[property] });\n        if (msg.payload[property]) {\n            var parts = property.split(\"/\");\n            if (parts[0]===\"period\") {\n                switch (parts[1]) {\n                    case \"today\":\n                        fromdate = epoch-Day;\n                        enddate = epoch;\n                        break;\n                    case \"yesterday\":\n                        fromdate = epoch-2*Day;\n                        enddate = epoch-Day;\n                        break;\n                    case \"week\":\n                        fromdate = epoch-Week;\n                        enddate = epoch;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM data_table WHERE device_id='\"+parts[0]+\"' AND data_type='\"+parts[1]+\"' AND data_time >= \" + fromdate + \" AND data_time <= \" + enddate+\";\" });\n            }\n        }\n    }\n}\n\noutput[output.length-1].complete=true;\n//output.complete=true;\nreturn msg;\n\n//msg.topic = \"SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= \" + fromdate + \" AND epoch <= \" + epoch ;\n//return msg;","outputs":1,"noerr":0,"x":344.9999694824219,"y":1364.0000610351562,"wires":[["663417ba.766f88","c40cdbaa.ba9e58"]]},{"id":"663417ba.766f88","type":"sqlite","z":"d8a790c3.eeaf8","mydb":"a1c6271f.dbd018","name":"DB","x":532.9999694824219,"y":1369.0000610351562,"wires":[["e2bd5ea9.a8afa","638468c3.c64d98"]]},{"id":"968bd68c.8f5ee8","type":"ui_chart","z":"d8a790c3.eeaf8","name":"Chart","group":"bd17ce5.f2bb93","order":0,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"%a %H:%M","interpolate":"linear","nodata":"","ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"x":1067.9999694824219,"y":1483,"wires":[[],[]]},{"id":"bd99849d.c9b898","type":"function","z":"d8a790c3.eeaf8","name":"Prep","func":"var msg2 = [];\n\n// this is the logic when there are multiple data sets are received\nfor (var i=0; i<msg.payload.length; i++) {\n    var output = [];\n    for (var j=0; j<msg.payload[i].length; j++) {\n        output.push([msg.payload[i][j].data_time, msg.payload[i][j].data_value]);\n    }\n    msg2.push({ key: msg.payload[i][0].device_id+\"/\"+msg.payload[i][0].data_type, values : output});\n    //msg2.push({ key: \"test\", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":863.9999694824219,"y":1371.0000610351562,"wires":[["968bd68c.8f5ee8","d7bea1e1.094c"]]},{"id":"37d0efd5.fa239","type":"inject","z":"d8a790c3.eeaf8","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":221.99996948242188,"y":1491.0000610351562,"wires":[["80eb3406.77d4f8"]]},{"id":"80eb3406.77d4f8","type":"function","z":"d8a790c3.eeaf8","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":549.9999694824219,"y":1490.0000610351562,"wires":[["968bd68c.8f5ee8"]]},{"id":"c7e866f5.1a7f18","type":"ui_form","z":"d8a790c3.eeaf8","name":"Data points","label":"","group":"c9e870b.7e4249","order":0,"width":0,"height":0,"options":[{"label":"Period: today","value":"period/today","type":"checkbox","required":false},{"label":"Period: yesterday","value":"period/yesterday","type":"checkbox","required":false},{"label":"Period: this week","value":"period/week","type":"checkbox","required":false},{"label":"device: 001","value":"device","type":"checkbox","required":false}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"device":false},"payload":"","topic":"","x":91.99996948242188,"y":1327.0000610351562,"wires":[["8de8df24.2626e","133a6ba4.de3444"]]},{"id":"e2bd5ea9.a8afa","type":"join","z":"d8a790c3.eeaf8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":688.9999694824219,"y":1369.0000610351562,"wires":[["bd99849d.c9b898","e94ec30c.9cd5f"]]},{"id":"d7bea1e1.094c","type":"debug","z":"d8a790c3.eeaf8","name":"","active":true,"console":"false","complete":"false","x":1072.9999694824219,"y":1329.0000610351562,"wires":[]},{"id":"638468c3.c64d98","type":"debug","z":"d8a790c3.eeaf8","name":"","active":true,"console":"false","complete":"false","x":658.9999694824219,"y":1293.0000610351562,"wires":[]},{"id":"c40cdbaa.ba9e58","type":"debug","z":"d8a790c3.eeaf8","name":"","active":true,"console":"false","complete":"topic","x":451.9999694824219,"y":1281.0000610351562,"wires":[]},{"id":"e94ec30c.9cd5f","type":"debug","z":"d8a790c3.eeaf8","name":"","active":true,"console":"false","complete":"false","x":882.9999694824219,"y":1285.0000610351562,"wires":[]},{"id":"133a6ba4.de3444","type":"debug","z":"d8a790c3.eeaf8","name":"","active":true,"console":"false","complete":"false","x":260.9999694824219,"y":1279.0000610351562,"wires":[]},{"id":"b810aac8.fb1488","type":"inject","z":"d8a790c3.eeaf8","name":"inject JSON object","topic":"","payload":"{ \"period/today\": true, \"period/yesterday\": false, \"period/week\": false, \"device\": false }","payloadType":"json","repeat":"","crontab":"","once":false,"x":158.99996948242188,"y":1394,"wires":[["8de8df24.2626e"]]},{"id":"a1c6271f.dbd018","type":"sqlitedb","z":"","db":"/home/pi/pfe.db"},{"id":"bd17ce5.f2bb93","type":"ui_group","z":"","name":"Report","tab":"ba5c229.a81c3e","order":2,"disp":true,"width":"18"},{"id":"c9e870b.7e4249","type":"ui_group","z":"","name":"Selection","tab":"ba5c229.a81c3e","order":1,"disp":true,"width":"5"},{"id":"ba5c229.a81c3e","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9}] 

[Colin Law]

In one of the places you are using complete (eg
output[output.length-1].complete) the variable you are calling
complete on is not defined (so if it is the one I have shown then
output[output.length-1] is undefined. Note it may not be that one
though. You can insert statements like
node.warn("some string");
in your code to show the value of variables to help home in on this.
Also you can include the values of variables in the string using
appropriate javascript.

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---

> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.

> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/eb0a729c-47be-4728-aa3f-ea58e7d8faeb%40googlegroups.com.

[Paul Woodard]

I think you are missing something from the OP's table. If you change the json inject to { "period/today": true, "period/yesterday": false, "period/week": false, "device": false, "complete":true } the error goes away. 

[Csongor Varga]

Hi Remi,

Sorry for not picking this up earlier, I had issues with my own node-red instance. The problem is the following: I have designed this knowledge in mind that my data points are linked to a device and sensor pair. In my database table I always have a device and a sensor field. And for the same reason when I define the data source, I used the device/sensor as an ID in dropdown. The code is looking for the '/' character to identify that it is a data source ID. In your example your device 001 name is only "device" so the code does not see it as a "data source id". I think you need to change the name to at least "device/" for the split at line 14 to work, otherwise no SQL statement will be generated.

You are getting this error because there is no SQL statements generated, so the output object is empty, hence the attribute set command fails.

I hope this makes sense now.

Regards,

Csongor

[remi richard]

Hi Csongor, 

I eventually found that. I made a few quick changes and the report works great.Very useful reporting flow.

Thanks for the response. it may help a few more people in the future. 

[clement.c...@gmail.com]

Hi guys,

I'm a french student and I'm learning about node-red. I've a projet which is to draw a draft with data come from SQLITE.

The plateform is a Raspberry PI 3.

I've different sensors (temperature, water level, electric current etc..).

I would like to use what you've done but I don't really understand the code. I've download sqlite module on NodeRed and I tried to use it but it's difficult for me.

My datas in collumns look like that :

Date (dd/mm/yyyy) ||| Time (hh:mm:ss) ||| temperature values ||| water level ||| electric current

I would like to have possibility to pick between two dates (from - to) and to add day too and different sensors like Csongor Varga.

Can somebody help me ??

[joseph stiven cardona vega]

Hi, I'm using the example you kindly shared, I currently have an error in the "Prep" block, where it does not recognize the sensor and device variables. Could you explain to me in what format the data should reach this block? Thank you

[Zenofmud]

Since I don’t see any reference to ‘sensor’ could you please export your flow and attach it to a reply (go to the flow, select all then go to the hamburger menu ->export->clipboard and copy and paste what you have to a reply.

-- 
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
--- 
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/a280f20d-3630-4b08-b637-f0449839408b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

<Captura de pantalla de 2018-05-05 19-08-54.png>

[joseph stiven cardona vega]

Thanks for your prompt response Paul, the error was in the for cycle within the Prep block. so the database does not always throw the information into the organization of the initially proposed matrix, msg2.push ({key: msg.payload [i] [0] .device + \ "/ \" + msg.payload [i ] [0] .sensor, values: output});, in my case, the correct path of the matrix is: msg2. push ({key: msg.payload [1] [i] .device + "/" + msg.payload [1] [i] .sensor, values: output}) ;.

For this reason, it did not initially recognize the sensor and device variables. since I did not find these variables in the path of the for cycle.

Sorry for the English, I do not handle it correctly, so I support the google translator.