How To Make Logging Data Values in Chart

General
1

Hi.

I have flow and I can logging values in DB.

But any specific time I want to choose with creating form node name and time specific ( Laser 1 - Last week values ) and make a graphic.

So this is my next step. Thanks for your advance.

I watched Csongor Varga videos, but didnt edited for my flow.

[{"id":"89143d1f.81909","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":150,"y":40,"wires":[["25f65457.f009ec"]]},{"id":"25f65457.f009ec","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n    }\n}","x":336,"y":38,"wires":[["b917b1a8.3d78","43ba6921.5d63a8"]]},{"id":"b917b1a8.3d78","type":"ui_button","z":"e98d7f.572f328","name":"Laser 1","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":40,"wires":[[]]},{"id":"43ba6921.5d63a8","type":"function","z":"e98d7f.572f328","name":"insert Statement","func":"var date = Date.now();\n\nvar newMsg = {\n    \"topic\": \"INSERT INTO Test VALUES (CURRENT_TIMESTAMP , \\\"\" +\n    msg.topic + \"\\\",\\\"\"  + \n    msg.label + \"\\\",\" + msg.payload + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":780,"y":160,"wires":[["c7306518.da4dc8"]]},{"id":"c7306518.da4dc8","type":"sqlite","z":"e98d7f.572f328","mydb":"4d41bb29.606284","sqlquery":"msg.topic","sql":"","name":"Record","x":960,"y":140,"wires":[[]]},{"id":"842c8b8f.634f88","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"38","intype":"tri","debounce":"25","read":false,"x":150,"y":80,"wires":[["f5117041.947a5"]]},{"id":"f5117041.947a5","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n       \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 2 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 2 Error!!!';\"\n    }\n}","x":336,"y":78,"wires":[["43ba6921.5d63a8","b83da6c6.ffea08"]]},{"id":"f406f6cf.304028","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"36","intype":"tri","debounce":"25","read":false,"x":150,"y":120,"wires":[["9eab3ec4.6374c"]]},{"id":"9eab3ec4.6374c","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 3 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 3 Error!!!';\"\n    }\n}","x":336,"y":118,"wires":[["43ba6921.5d63a8","1b04f4ca.92a35b"]]},{"id":"b54359e4.25e4c8","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"35","intype":"tri","debounce":"25","read":false,"x":150,"y":160,"wires":[["9d9c3919.a8f488"]]},{"id":"9d9c3919.a8f488","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 4 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 4 Error!!!';\"\n    }\n}","x":336,"y":158,"wires":[["43ba6921.5d63a8","f2279766.617078"]]},{"id":"c6d263c8.c775c","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"33","intype":"tri","debounce":"25","read":false,"x":150,"y":200,"wires":[["6a2bbf8d.a2274"]]},{"id":"6a2bbf8d.a2274","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 5 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 5 Error!!!';\"\n    }\n}","x":336,"y":198,"wires":[["43ba6921.5d63a8","ed194baa.78a578"]]},{"id":"715b1ad3.b24ed4","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"32","intype":"tri","debounce":"25","read":false,"x":150,"y":240,"wires":[["edcde1e6.2c42b"]]},{"id":"edcde1e6.2c42b","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 6 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 6 Error!!!';\"\n    }\n}","x":336,"y":238,"wires":[["43ba6921.5d63a8","6f61fcc5.29eb94"]]},{"id":"b83da6c6.ffea08","type":"ui_button","z":"e98d7f.572f328","name":"Laser 2","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":80,"wires":[[]]},{"id":"1b04f4ca.92a35b","type":"ui_button","z":"e98d7f.572f328","name":"Laser 3","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":120,"wires":[[]]},{"id":"f2279766.617078","type":"ui_button","z":"e98d7f.572f328","name":"Laser 4","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":160,"wires":[[]]},{"id":"ed194baa.78a578","type":"ui_button","z":"e98d7f.572f328","name":"Laser 5","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":200,"wires":[[]]},{"id":"6f61fcc5.29eb94","type":"ui_button","z":"e98d7f.572f328","name":"Laser 6","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":240,"wires":[[]]},{"id":"c5de76cd.47aa08","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"31","intype":"tri","debounce":"25","read":false,"x":150,"y":280,"wires":[["c17a729f.1cb35"]]},{"id":"c17a729f.1cb35","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 7 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 7 Error!!!';\"\n    }\n}","x":336,"y":278,"wires":[["fb5f3809.a20b28","43ba6921.5d63a8"]]},{"id":"fb5f3809.a20b28","type":"ui_button","z":"e98d7f.572f328","name":"Laser 7","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":280,"wires":[[]]},{"id":"37b9df38.4e2e3","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"29","intype":"tri","debounce":"25","read":false,"x":150,"y":320,"wires":[["6ce1c9b2.785ee8"]]},{"id":"6ce1c9b2.785ee8","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 8 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 8 Error!!!';\"\n    }\n}","x":330,"y":320,"wires":[["485c896.17da478","43ba6921.5d63a8"]]},{"id":"485c896.17da478","type":"ui_button","z":"e98d7f.572f328","name":"Laser 8","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":320,"wires":[[]]},{"id":"b5c34cd0.ec17a","type":"rpi-dht22","z":"e98d7f.572f328","name":"","topic":"Dht11","dht":"11","pintype":"0","pin":4,"x":350,"y":420,"wires":[["c602583d.526898","67699b97.bc7a94"]]},{"id":"cdf5fd0f.91f61","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"7","intype":"tri","debounce":"25","read":false,"x":140,"y":446.0000171661377,"wires":[["b5c34cd0.ec17a"]]},{"id":"c602583d.526898","type":"function","z":"e98d7f.572f328","name":"Sıcaklık","func":"msg.payload = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":520,"y":400,"wires":[["bc7fce8b.f562b","b3c2c2b7.d0c29"]]},{"id":"bc7fce8b.f562b","type":"ui_gauge","z":"e98d7f.572f328","name":"","group":"7778e462.3b95ac","order":0,"width":"4","height":"4","gtype":"gage","title":"Kabin içi sıcaklık","label":"°C","format":"{{value}}","min":0,"max":"40","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":690,"y":380,"wires":[]},{"id":"67699b97.bc7a94","type":"function","z":"e98d7f.572f328","name":"Nem","func":"msg.payload = msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":530,"y":440,"wires":[["b934cfb6.46d2d","b3c2c2b7.d0c29"]]},{"id":"b3c2c2b7.d0c29","type":"function","z":"e98d7f.572f328","name":"Function1","func":"var date = Date.now();\n\nvar newMsg = {\n    \"topic\": \"INSERT INTO testhumid VALUES (CURRENT_TIMESTAMP , \" + msg.payload + \", \" + msg.humidity + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":840,"y":400,"wires":[["33f9f4a0.e3670c"]]},{"id":"b9ecbe64.31974","type":"inject","z":"e98d7f.572f328","name":"","topic":"","payload":"","payloadType":"date","repeat":"30","crontab":"","once":false,"onceDelay":0.1,"x":164.60765075683594,"y":377.94922828674316,"wires":[["b5c34cd0.ec17a"]]},{"id":"b934cfb6.46d2d","type":"ui_gauge","z":"e98d7f.572f328","name":"","group":"7778e462.3b95ac","order":0,"width":"4","height":"4","gtype":"gage","title":"Kabin içi nem","label":"%","format":"{{value}}","min":0,"max":"60","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":680,"y":440,"wires":[]},{"id":"33f9f4a0.e3670c","type":"sqlite","z":"e98d7f.572f328","mydb":"fe628096.4632f","sqlquery":"msg.topic","sql":"","name":"Temp-Humid","x":1010,"y":380,"wires":[[]]},{"id":"d91a4965.29fb08","type":"function","z":"e98d7f.572f328","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar p_30d  = 1000*60*60*24*30 ; //30 Days\nvar p_7d  = 1000*60*60*24*7 ; //7 Days\nvar p_1d   =  1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n//var sourcelist = [];\nvar aggrlist = [];\nvar title = \"\";\n\n\n// Get the period and the list of data sources \n// also set some default values if one or the other does not exist yet\nsourcelist = context.get(\"sourcelist\");\nif (sourcelist===undefined) { // if running for the first time\n    sourcelist = [];\n}\naggrlist = context.get(\"aggrlist\");\nif (aggrlist===undefined) { // if running for the first time\n    aggrlist = [];\n}\nfromdate = context.get(\"fromdate\");\nif (fromdate===undefined) {\n    // set the period to a default if it is not selected yet\n    fromdate = current-p_1d;\n}\nenddate = context.get(\"enddate\");\nif (enddate===undefined) {\n    // set the period to a default if it is not selected yet\n    enddate = current;\n}\n\nswitch(msg.topic) {\n    case \"period\":\n        switch(msg.payload) {\n            case \"today\":\n                fromdate = today0h;\n                enddate = today0h+p_1d;\n                break;\n            case \"yesterday\":\n                fromdate = today0h-p_1d;\n                enddate = today0h;\n                break;\n            case \"thisweek\":\n                fromdate = monday0h;\n                enddate = monday0h+p_7d;\n                break;\n            case \"lastweek\":\n                fromdate = monday0h-p_7d;\n                enddate = monday0h;\n                break;\n            case \"last24h\":\n                fromdate = current-p_1d;\n                enddate = current;\n                break;\n            case \"last7d\":\n                fromdate = current-p_7d;\n                enddate = current;\n                break;\n            case \"last30d\":\n                fromdate = current-p_30d;\n                enddate = current;\n                break;\n        }\n        context.set(\"fromdate\",fromdate);\n        context.set(\"enddate\",enddate);\n        break;\n    case \"datasource\":\n        if (msg.payload===\"delete\") {\n            // remove all previous data sources\n            sourcelist = [];\n        } else {\n            sourcelist = context.get(\"sourcelist\");\n            if (sourcelist===undefined) { // if running for the first time\n                sourcelist = [];\n            }\n            sourcelist.push(msg.payload);\n        }\n        context.set(\"sourcelist\",sourcelist);\n        break;\n    case \"aggrsource\":\n        if (msg.payload===\"delete\") {\n            // remove all previous data sources\n            aggrlist = [];\n        } else {\n            aggrlist = context.get(\"aggrlist\");\n            if (aggrlist===undefined) { // if running for the first time\n                aggrlist = [];\n            }\n            aggrlist.push(msg.payload);\n        }\n        context.set(\"aggrlist\",aggrlist);\n        break;\n}\n\n// Regenerate the SQL statements\n// Run through the data source list an generate the SQL statements\nsql = [];\nif (sourcelist.length>0) {\n    for (var i = 0; i < sourcelist.length; i++) {\n        var parts = sourcelist[i].split(\"/\");\n       sql.push({ topic: \"SELECT Value FROM Test\" +value[0]+\"\"+value[1] });\n       \n    }\n} \n//if (aggrlist.length>0) {\n//    for (var i = 0; i < aggrlist.length; i++) {\n//        var parts = aggrlist[i].split(\"/\");\n//        sql.push({ topic: \"SELECT Value FROM Test\" +value[0]+\"\"+value[1] + fromdate + \"  \" + enddate });\n//    }\n//} \nif (sql.length===0) {    \n    // Dummy select that returns nothing to clear the chart\n    sql.push({ topic: \"SELECT Value FROM Test\" });\n}\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n// pass along the email flag to redirect the flow later\nif (msg.topic===\"email\") {\n    sql[sql.length-1].email=true;\n}\n\n// Generate report title\nif (sourcelist.length===0 && aggrlist.length===0) {\n    title = \"No data source\";\n} else {\n    if (sourcelist.length!==0) {\n        title = title + sourcelist.toString()+ \", \";\n    }\n    if (aggrlist.length!==0) {\n        title = title + aggrlist.toString()+ \", \";\n    }\n    title = title.substring(0,title.length-2);\n    title = title + \" | \";\n\n    var d = new Date();\n    d.setTime(fromdate);\n    var yyyy = d.getFullYear();\n    var mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n    var dd  = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n    var hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n    var mmm  = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n    var ss  = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n    title = title + dd + \".\" + mm + \".\" + yyyy;\n    d.setTime(enddate);\n    yyyy = d.getFullYear();\n    mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n    dd  = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n    hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n    mmm  = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n    ss  = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n    title = title + \" - \" + dd + \".\" + mm + \".\" + yyyy;\n}\nsql[sql.length-1].title=title;\n\nreturn [ sql ];\n\n","outputs":1,"noerr":0,"x":510,"y":640,"wires":[["52d9fcd4.207e54","ab88a38c.0f2b8"]]},{"id":"52d9fcd4.207e54","type":"sqlite","z":"e98d7f.572f328","mydb":"efeb70ef.e1718","sql":"","name":"DB","x":710,"y":640,"wires":[["dddb798e.261be8"]]},{"id":"2b2a552b.98ea9a","type":"ui_chart","z":"e98d7f.572f328","name":"Chart","group":"f49cf745.77c7e8","order":2,"width":"12","height":"10","label":"","chartType":"line","legend":"false","xformat":"dd HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1250,"y":640,"wires":[[],[]]},{"id":"dddb798e.261be8","type":"join","z":"e98d7f.572f328","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":870,"y":640,"wires":[["f723de6f.c4228","3b69233b.5f816c"]]},{"id":"f723de6f.c4228","type":"function","z":"e98d7f.572f328","name":"Craht Prep","func":"var msg2 = [];\n\nif (msg.payload[0].length>0) {\n    // this is the logic when there are multiple data sets are received\n    for (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} \n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":1050,"y":640,"wires":[["2b2a552b.98ea9a"]]},{"id":"3b69233b.5f816c","type":"change","z":"e98d7f.572f328","name":"Title","rules":[{"t":"set","p":"payload","pt":"msg","to":"title","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1030,"y":600,"wires":[["61e1a568.78b3bc"]]},{"id":"61e1a568.78b3bc","type":"ui_text","z":"e98d7f.572f328","group":"f49cf745.77c7e8","order":0,"width":0,"height":0,"name":"","label":"Chart Title","format":"{{msg.payload}}","layout":"row-center","x":1250,"y":600,"wires":[]},{"id":"847203aa.7c4d2","type":"inject","z":"e98d7f.572f328","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":true,"x":170,"y":540,"wires":[["5a5f2668.956768"]]},{"id":"2aa0e005.ba3a5","type":"change","z":"e98d7f.572f328","name":"Reset","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":130,"y":620,"wires":[["5a5f2668.956768"]]},{"id":"5a5f2668.956768","type":"ui_dropdown","z":"e98d7f.572f328","name":"Period","label":"","place":"","group":"f49cf745.77c7e8","order":4,"width":"4","height":"1","passthru":false,"options":[{"label":"Today","value":"today","type":"str"},{"label":"Yesterday","value":"yesterday","type":"str"},{"label":"This week","value":"thisweek","type":"str"},{"label":"Last week","value":"lastweek","type":"str"},{"label":"Last 24 hours","value":"last24h","type":"str"},{"label":"Last 7 days","value":"last7d","type":"str"},{"label":"Last 30 days","value":"last30d","type":"str"}],"payload":"","topic":"period","x":350,"y":600,"wires":[["d91a4965.29fb08"]]},{"id":"dbc7e6fa.a16f28","type":"function","z":"e98d7f.572f328","name":"Empty Pay","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":1050,"y":700,"wires":[["2b2a552b.98ea9a"]]},{"id":"79a91b81.e4ffa4","type":"inject","z":"e98d7f.572f328","name":"Reset Chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":870,"y":700,"wires":[["dbc7e6fa.a16f28"]]},{"id":"ab88a38c.0f2b8","type":"debug","z":"e98d7f.572f328","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":720,"y":580,"wires":[]},{"id":"7778e462.3b95ac","type":"ui_group","z":"","name":"Assembly Lines","tab":"6ed57066.2c961","disp":true,"width":"5","collapse":false},{"id":"4d41bb29.606284","type":"sqlitedb","z":"","db":"/var/www/html/database/Test","mode":"RWC"},{"id":"fe628096.4632f","type":"sqlitedb","z":"","db":"/var/www/html/database/testhumid","mode":"RWC"},{"id":"efeb70ef.e1718","type":"sqlitedb","z":"","db":"/var/www/html/database/Test"},{"id":"f49cf745.77c7e8","type":"ui_group","z":"","name":"Report","tab":"6ed57066.2c961","order":2,"disp":false,"width":"22","collapse":false},{"id":"6ed57066.2c961","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]
2

You need to be a little more specific about what problem you are having.

Did you try asking for help from the author on youtube?

3

I tried to ask his mail address but he didnt me answer. Maybe he is busy or mail is incorrect.

My database name is " Test " and my column name is " Value "
I want to take vlues this db and column for a specif time, for example today, just want to see this value in Chart.

Here is my function node but something wrong I dont know where is it.

// This will handle any device and any attribute as long as it is in the DB
var p_30d  = 1000*60*60*24*30 ; //30 Days
var p_7d  = 1000*60*60*24*7 ; //7 Days
var p_1d   =  1000*60*60*24 ; // 1 Day
var d = new Date();
var current = d.getTime();
var today0h = d.setHours(0,0,0,0);
var day = d.getDay();
var monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;
var fromdate = 0;
var enddate = 0;
var sql = [];
//var sourcelist = [];
var aggrlist = [];
var title = "";


// Get the period and the list of data sources 
// also set some default values if one or the other does not exist yet
sourcelist = context.get("sourcelist");
if (sourcelist===undefined) { // if running for the first time
    sourcelist = [];
}
aggrlist = context.get("aggrlist");
if (aggrlist===undefined) { // if running for the first time
    aggrlist = [];
}
fromdate = context.get("fromdate");
if (fromdate===undefined) {
    // set the period to a default if it is not selected yet
    fromdate = current-p_1d;
}
enddate = context.get("enddate");
if (enddate===undefined) {
    // set the period to a default if it is not selected yet
    enddate = current;
}

switch(msg.topic) {
    case "period":
        switch(msg.payload) {
            case "today":
                fromdate = today0h;
                enddate = today0h+p_1d;
                break;
            case "yesterday":
                fromdate = today0h-p_1d;
                enddate = today0h;
                break;
            case "thisweek":
                fromdate = monday0h;
                enddate = monday0h+p_7d;
                break;
            case "lastweek":
                fromdate = monday0h-p_7d;
                enddate = monday0h;
                break;
            case "last24h":
                fromdate = current-p_1d;
                enddate = current;
                break;
            case "last7d":
                fromdate = current-p_7d;
                enddate = current;
                break;
            case "last30d":
                fromdate = current-p_30d;
                enddate = current;
                break;
        }
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
    case "datasource":
        if (msg.payload==="delete") {
            // remove all previous data sources
            sourcelist = [];
        } else {
            sourcelist = context.get("sourcelist");
            if (sourcelist===undefined) { // if running for the first time
                sourcelist = [];
            }
            sourcelist.push(msg.payload);
        }
        context.set("sourcelist",sourcelist);
        break;
    case "aggrsource":
        if (msg.payload==="delete") {
            // remove all previous data sources
            aggrlist = [];
        } else {
            aggrlist = context.get("aggrlist");
            if (aggrlist===undefined) { // if running for the first time
                aggrlist = [];
            }
            aggrlist.push(msg.payload);
        }
        context.set("aggrlist",aggrlist);
        break;
}

// Regenerate the SQL statements
// Run through the data source list an generate the SQL statements
sql = [];
if (sourcelist.length>0) {
    for (var i = 0; i < sourcelist.length; i++) {
        var parts = sourcelist[i].split("/");
       // sql.push({ topic: "SELECT Value FROM Test" +value[0]+""+value[1]+ fromdate + "  " + enddate });
        sql.push({ topic: "SELECT * FROM Test WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
    }
} 
//if (aggrlist.length>0) {
//    for (var i = 0; i < aggrlist.length; i++) {
//        var parts = aggrlist[i].split("/");
//        sql.push({ topic: "SELECT Value FROM Test" +value[0]+""+value[1] + fromdate + "  " + enddate });
//    }
//} 
if (sql.length===0) {    
    // Dummy select that returns nothing to clear the chart
    sql.push({ topic: "SELECT Value FROM Test" });
}

// set the completed flag for the join node later
sql[sql.length-1].complete=true;
// pass along the email flag to redirect the flow later
if (msg.topic==="email") {
    sql[sql.length-1].email=true;
}

// Generate report title
if (sourcelist.length===0 && aggrlist.length===0) {
    title = "No data source";
} else {
    if (sourcelist.length!==0) {
        title = title + sourcelist.toString()+ ", ";
    }
    if (aggrlist.length!==0) {
        title = title + aggrlist.toString()+ ", ";
    }
    title = title.substring(0,title.length-2);
    title = title + " | ";

    var d = new Date();
    d.setTime(fromdate);
    var yyyy = d.getFullYear();
    var mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
    var dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
    var hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
    var mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
    var ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
    title = title + dd + "." + mm + "." + yyyy;
    d.setTime(enddate);
    yyyy = d.getFullYear();
    mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
    dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
    hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
    mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
    ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
    title = title + " - " + dd + "." + mm + "." + yyyy;
}
sql[sql.length-1].title=title;

return [ sql ];
4

if you put a debug node on the output of this function (set it to display the complete msg object) what does it show?

Have you tried creating another node where you build the sql by using the actual values and seeing if it returnes the data you want?

By breaking the issue into smaller pieces, it will make it easier to solve.

5

I didnt tried to build another node, as I said before I dont have enough experince and trying to make something.

Can you give me or suggest to some how to be proccess ?
Here is debug node screenshot.

Untitled
Untitled.png296×510 13.4 KB

6

you don't have to create a node, use the existing ones. Take a look in the flows tab of this site and enter mysql and uncheck the 'nodes' box and try a few of the examples.

7

If possible I want to use litesql because I did something with litesql and now I want to solve this with sqlite

8

I don't think it is clear which part of the problem you are having difficulty with.
Are you able to get the data that you want from the db?

9

Ummm, I can't find any node with litesql as part of the name...did you mean you are using the node-red-node-sqlite?

Also, please your flow. If you are not sure how, please see How to share code or flow json

10

I can save data in db it is ok, but especially sql side is not clear for me, after saving data, I want to take values and make a chart for specific time, like today, yesterday last week... etc.

11

So I created my project with sqlite and trying to go with it.
Here is my flow and I think I have to change 2 function node inside ( showed in picture ), but where is it I dont know.

[{"id":"89143d1f.81909","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":150,"y":40,"wires":[["25f65457.f009ec"]]},{"id":"25f65457.f009ec","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 1 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1 Error!!!';\"\n    }\n}","x":336,"y":38,"wires":[["b917b1a8.3d78","43ba6921.5d63a8"]]},{"id":"b917b1a8.3d78","type":"ui_button","z":"e98d7f.572f328","name":"Laser 1","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":40,"wires":[[]]},{"id":"43ba6921.5d63a8","type":"function","z":"e98d7f.572f328","name":"insert Statement","func":"var newMsg = {\n    \"topic\": \"INSERT INTO Test VALUES (CURRENT_TIMESTAMP , \\\"\" +\n    msg.topic + \"\\\",\\\"\"  + \n    msg.label + \"\\\",\" + msg.payload + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":780,"y":160,"wires":[["c7306518.da4dc8"]]},{"id":"c7306518.da4dc8","type":"sqlite","z":"e98d7f.572f328","mydb":"4d41bb29.606284","sqlquery":"msg.topic","sql":"","name":"Record","x":960,"y":140,"wires":[[]]},{"id":"842c8b8f.634f88","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"38","intype":"tri","debounce":"25","read":false,"x":150,"y":80,"wires":[["f5117041.947a5"]]},{"id":"f5117041.947a5","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n       \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 2 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 2 Error!!!';\"\n    }\n}","x":336,"y":78,"wires":[["43ba6921.5d63a8","b83da6c6.ffea08"]]},{"id":"f406f6cf.304028","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"36","intype":"tri","debounce":"25","read":false,"x":150,"y":120,"wires":[["9eab3ec4.6374c"]]},{"id":"9eab3ec4.6374c","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 3 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 3 Error!!!';\"\n    }\n}","x":336,"y":118,"wires":[["43ba6921.5d63a8","1b04f4ca.92a35b"]]},{"id":"b54359e4.25e4c8","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"35","intype":"tri","debounce":"25","read":false,"x":150,"y":160,"wires":[["9d9c3919.a8f488"]]},{"id":"9d9c3919.a8f488","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 4 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 4 Error!!!';\"\n    }\n}","x":336,"y":158,"wires":[["43ba6921.5d63a8","f2279766.617078"]]},{"id":"c6d263c8.c775c","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"33","intype":"tri","debounce":"25","read":false,"x":150,"y":200,"wires":[["6a2bbf8d.a2274"]]},{"id":"6a2bbf8d.a2274","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 5 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 5 Error!!!';\"\n    }\n}","x":336,"y":198,"wires":[["43ba6921.5d63a8","ed194baa.78a578"]]},{"id":"715b1ad3.b24ed4","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"32","intype":"tri","debounce":"25","read":false,"x":150,"y":240,"wires":[["edcde1e6.2c42b"]]},{"id":"edcde1e6.2c42b","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 6 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 6 Error!!!';\"\n    }\n}","x":336,"y":238,"wires":[["43ba6921.5d63a8","6f61fcc5.29eb94"]]},{"id":"b83da6c6.ffea08","type":"ui_button","z":"e98d7f.572f328","name":"Laser 2","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":80,"wires":[[]]},{"id":"1b04f4ca.92a35b","type":"ui_button","z":"e98d7f.572f328","name":"Laser 3","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":120,"wires":[[]]},{"id":"f2279766.617078","type":"ui_button","z":"e98d7f.572f328","name":"Laser 4","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":160,"wires":[[]]},{"id":"ed194baa.78a578","type":"ui_button","z":"e98d7f.572f328","name":"Laser 5","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":200,"wires":[[]]},{"id":"6f61fcc5.29eb94","type":"ui_button","z":"e98d7f.572f328","name":"Laser 6","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":240,"wires":[[]]},{"id":"c5de76cd.47aa08","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"31","intype":"tri","debounce":"25","read":false,"x":150,"y":280,"wires":[["c17a729f.1cb35"]]},{"id":"c17a729f.1cb35","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 7 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 7 Error!!!';\"\n    }\n}","x":336,"y":278,"wires":[["fb5f3809.a20b28","43ba6921.5d63a8"]]},{"id":"fb5f3809.a20b28","type":"ui_button","z":"e98d7f.572f328","name":"Laser 7","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":280,"wires":[[]]},{"id":"37b9df38.4e2e3","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"29","intype":"tri","debounce":"25","read":false,"x":150,"y":320,"wires":[["6ce1c9b2.785ee8"]]},{"id":"6ce1c9b2.785ee8","type":"dsm","z":"e98d7f.572f328","name":"state right","sm_config":"{\n    \"triggerInput\": \"payload\",\n    \"currentState\": \"running\",\n    \"states\": {\n        \"stopped\": {\n            \"0\": \"running\"\n        },\n        \"running\": {\n            \"1\": \"stopped\"\n        }\n    },\n    \"methods\": {\n        \"0\": \"sta.fill='green';msg.background='green';msg.label='Laser 8 Working';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 8 Error!!!';\"\n    }\n}","x":330,"y":320,"wires":[["485c896.17da478","43ba6921.5d63a8"]]},{"id":"485c896.17da478","type":"ui_button","z":"e98d7f.572f328","name":"Laser 8","group":"7778e462.3b95ac","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":520,"y":320,"wires":[[]]},{"id":"b5c34cd0.ec17a","type":"rpi-dht22","z":"e98d7f.572f328","name":"","topic":"Dht11","dht":"11","pintype":"0","pin":4,"x":350,"y":460,"wires":[["c602583d.526898","67699b97.bc7a94"]]},{"id":"cdf5fd0f.91f61","type":"rpi-gpio in","z":"e98d7f.572f328","name":"","pin":"7","intype":"tri","debounce":"25","read":false,"x":140,"y":486.0000171661377,"wires":[["b5c34cd0.ec17a"]]},{"id":"c602583d.526898","type":"function","z":"e98d7f.572f328","name":"Sıcaklık","func":"msg.payload = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":520,"y":440,"wires":[["bc7fce8b.f562b","b3c2c2b7.d0c29"]]},{"id":"bc7fce8b.f562b","type":"ui_gauge","z":"e98d7f.572f328","name":"","group":"7778e462.3b95ac","order":0,"width":"4","height":"4","gtype":"gage","title":"Kabin içi sıcaklık","label":"°C","format":"{{value}}","min":0,"max":"40","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":690,"y":420,"wires":[]},{"id":"67699b97.bc7a94","type":"function","z":"e98d7f.572f328","name":"Nem","func":"msg.payload = msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":530,"y":480,"wires":[["b934cfb6.46d2d","b3c2c2b7.d0c29"]]},{"id":"b3c2c2b7.d0c29","type":"function","z":"e98d7f.572f328","name":"Function1","func":"var date = Date.now();\n\nvar newMsg = {\n    \"topic\": \"INSERT INTO testhumid VALUES (CURRENT_TIMESTAMP , \" + msg.payload + \", \" + msg.humidity + \")\"\n}\n\nreturn newMsg;","outputs":1,"noerr":0,"x":840,"y":440,"wires":[["33f9f4a0.e3670c"]]},{"id":"b9ecbe64.31974","type":"inject","z":"e98d7f.572f328","name":"","topic":"","payload":"","payloadType":"date","repeat":"30","crontab":"","once":false,"onceDelay":0.1,"x":164.60765075683594,"y":417.94922828674316,"wires":[["b5c34cd0.ec17a"]]},{"id":"b934cfb6.46d2d","type":"ui_gauge","z":"e98d7f.572f328","name":"","group":"7778e462.3b95ac","order":0,"width":"4","height":"4","gtype":"gage","title":"Kabin içi nem","label":"%","format":"{{value}}","min":0,"max":"60","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":680,"y":480,"wires":[]},{"id":"33f9f4a0.e3670c","type":"sqlite","z":"e98d7f.572f328","mydb":"fe628096.4632f","sqlquery":"msg.topic","sql":"","name":"Temp-Humid","x":1010,"y":420,"wires":[[]]},{"id":"71308322.8eb3bc","type":"function","z":"e98d7f.572f328","name":"SQL","func":"// This will handle any device and any attribute as long as it is in the DB\nvar p_30d  = 1000*60*60*24*30 ; //30 Days\nvar p_7d  = 1000*60*60*24*7 ; //7 Days\nvar p_1d   =  1000*60*60*24 ; // 1 Day\nvar d = new Date();\nvar current = d.getTime();\nvar today0h = d.setHours(0,0,0,0);\nvar day = d.getDay();\nvar monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;\nvar fromdate = 0;\nvar enddate = 0;\nvar sql = [];\n//var sourcelist = [];\nvar aggrlist = [];\nvar title = \"\";\n\n\n// Get the period and the list of data sources \n// also set some default values if one or the other does not exist yet\nsourcelist = context.get(\"sourcelist\");\nif (sourcelist===undefined) { // if running for the first time\n    sourcelist = [];\n}\naggrlist = context.get(\"aggrlist\");\nif (aggrlist===undefined) { // if running for the first time\n    aggrlist = [];\n}\nfromdate = context.get(\"fromdate\");\nif (fromdate===undefined) {\n    // set the period to a default if it is not selected yet\n    fromdate = current-p_1d;\n}\nenddate = context.get(\"enddate\");\nif (enddate===undefined) {\n    // set the period to a default if it is not selected yet\n    enddate = current;\n}\n\nswitch(msg.topic) {\n    case \"period\":\n        switch(msg.payload) {\n            case \"today\":\n                fromdate = today0h;\n                enddate = today0h+p_1d;\n                break;\n            case \"yesterday\":\n                fromdate = today0h-p_1d;\n                enddate = today0h;\n                break;\n            case \"thisweek\":\n                fromdate = monday0h;\n                enddate = monday0h+p_7d;\n                break;\n            case \"lastweek\":\n                fromdate = monday0h-p_7d;\n                enddate = monday0h;\n                break;\n            case \"last24h\":\n                fromdate = current-p_1d;\n                enddate = current;\n                break;\n            case \"last7d\":\n                fromdate = current-p_7d;\n                enddate = current;\n                break;\n            case \"last30d\":\n                fromdate = current-p_30d;\n                enddate = current;\n                break;\n        }\n        context.set(\"fromdate\",fromdate);\n        context.set(\"enddate\",enddate);\n        break;\n    case \"datasource\":\n        if (msg.payload===\"delete\") {\n            // remove all previous data sources\n            sourcelist = [];\n        } else {\n            sourcelist = context.get(\"sourcelist\");\n            if (sourcelist===undefined) { // if running for the first time\n                sourcelist = [];\n            }\n            sourcelist.push(msg.payload);\n        }\n        context.set(\"sourcelist\",sourcelist);\n        break;\n    case \"aggrsource\":\n        if (msg.payload===\"delete\") {\n            // remove all previous data sources\n            aggrlist = [];\n        } else {\n            aggrlist = context.get(\"aggrlist\");\n            if (aggrlist===undefined) { // if running for the first time\n                aggrlist = [];\n            }\n            aggrlist.push(msg.payload);\n        }\n        context.set(\"aggrlist\",aggrlist);\n        break;\n}\n\n// Regenerate the SQL statements\n// Run through the data source list an generate the SQL statements\nsql = [];\nif (sourcelist.length>0) {\n    for (var i = 0; i < sourcelist.length; i++) {\n        var parts = sourcelist[i].split(\"/\");\n       // sql.push({ topic: \"SELECT Value FROM Test\" +value[0]+\"\"+value[1]+ fromdate + \"  \" + enddate });\n        sql.push({ topic: \"SELECT * FROM Test WHERE device='\"+parts[0]+\"' AND sensor='\"+parts[1]+\"' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n    }\n} \n//if (aggrlist.length>0) {\n//    for (var i = 0; i < aggrlist.length; i++) {\n//        var parts = aggrlist[i].split(\"/\");\n//        sql.push({ topic: \"SELECT Value FROM Test\" +value[0]+\"\"+value[1] + fromdate + \"  \" + enddate });\n//    }\n//} \nif (sql.length===0) {    \n    // Dummy select that returns nothing to clear the chart\n    sql.push({ topic: \"SELECT Value FROM Test\" });\n}\n\n// set the completed flag for the join node later\nsql[sql.length-1].complete=true;\n// pass along the email flag to redirect the flow later\nif (msg.topic===\"email\") {\n    sql[sql.length-1].email=true;\n}\n\n// Generate report title\nif (sourcelist.length===0 && aggrlist.length===0) {\n    title = \"No data source\";\n} else {\n    if (sourcelist.length!==0) {\n        title = title + sourcelist.toString()+ \", \";\n    }\n    if (aggrlist.length!==0) {\n        title = title + aggrlist.toString()+ \", \";\n    }\n    title = title.substring(0,title.length-2);\n    title = title + \" | \";\n\n    var d = new Date();\n    d.setTime(fromdate);\n    var yyyy = d.getFullYear();\n    var mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n    var dd  = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n    var hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n    var mmm  = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n    var ss  = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n    title = title + dd + \".\" + mm + \".\" + yyyy;\n    d.setTime(enddate);\n    yyyy = d.getFullYear();\n    mm = d.getMonth() < 9 ? \"0\" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based\n    dd  = d.getDate() < 10 ? \"0\" + d.getDate() : d.getDate();\n    hh = d.getHours() < 10 ? \"0\" + d.getHours() : d.getHours();\n    mmm  = d.getMinutes() < 10 ? \"0\" + d.getMinutes() : d.getMinutes();\n    ss  = d.getSeconds() < 10 ? \"0\" + d.getSeconds() : d.getSeconds();\n    title = title + \" - \" + dd + \".\" + mm + \".\" + yyyy;\n}\nsql[sql.length-1].title=title;\n\nreturn [ sql ];","outputs":1,"noerr":0,"x":390,"y":780,"wires":[["5255c19a.162da"]]},{"id":"5255c19a.162da","type":"sqlite","z":"e98d7f.572f328","mydb":"4f589ff5.47b02","sql":"","name":"DB","x":530,"y":780,"wires":[["7bc57b7a.c175f4"]]},{"id":"a857ed92.8fda9","type":"ui_chart","z":"e98d7f.572f328","name":"Chart","group":"f49cf745.77c7e8","order":2,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"dd HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1025.6666946411133,"y":819.2499485015869,"wires":[[],[]]},{"id":"a2cc305b.34cb4","type":"function","z":"e98d7f.572f328","name":"Chart Prep","func":"var msg2 = [];\n\nif (msg.payload[0].length>0) {\n    // this is the logic when there are multiple data sets are received\n    for (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} \n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"x":852.8334121704102,"y":819.5833225250244,"wires":[["a857ed92.8fda9"]]},{"id":"db1a4bba.a42168","type":"inject","z":"e98d7f.572f328","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":550.7499923706055,"y":877.0000095367432,"wires":[["9c8004c5.32cf68"]]},{"id":"9c8004c5.32cf68","type":"function","z":"e98d7f.572f328","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":798.6666641235352,"y":876.3333234786987,"wires":[["a857ed92.8fda9"]]},{"id":"7bc57b7a.c175f4","type":"join","z":"e98d7f.572f328","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":663,"y":780,"wires":[["42416ea5.ebdfe","a2cc305b.34cb4"]]},{"id":"bcf07a31.f4cee8","type":"ui_dropdown","z":"e98d7f.572f328","name":"Data source","label":"","place":"","group":"f49cf745.77c7e8","order":1,"width":"5","height":"1","passthru":false,"options":[{"label":"[Remove all]","value":"delete","type":"str"},{"label":"Miflora temperature","value":"miflora/temp","type":"str"},{"label":"Miflora battery level","value":"miflora/battery","type":"str"},{"label":"Miflora sunlight","value":"miflora/sunlight","type":"str"},{"label":"Miflora soil moisture","value":"miflora/moisture","type":"str"},{"label":"Miflora soil fertility","value":"miflora/fertility","type":"str"},{"label":"Solar power","value":"growatt/power","type":"str"},{"label":"Solar voltage","value":"growatt/voltage","type":"str"}],"payload":"","topic":"datasource","x":560.25,"y":592.75,"wires":[["71308322.8eb3bc"]]},{"id":"9183e064.d8367","type":"ui_dropdown","z":"e98d7f.572f328","name":"Period","label":"","place":"","group":"f49cf745.77c7e8","order":4,"width":"4","height":"1","passthru":false,"options":[{"label":"Today","value":"today","type":"str"},{"label":"Yesterday","value":"yesterday","type":"str"},{"label":"This week","value":"thisweek","type":"str"},{"label":"Last week","value":"lastweek","type":"str"},{"label":"Last 24 hours","value":"last24h","type":"str"},{"label":"Last 7 days","value":"last7d","type":"str"},{"label":"Last 30 days","value":"last30d","type":"str"}],"payload":"","topic":"period","x":539.25,"y":635.75,"wires":[["71308322.8eb3bc"]]},{"id":"cd3099a.e5d2368","type":"inject","z":"e98d7f.572f328","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":true,"x":351.25,"y":575.75,"wires":[["bcf07a31.f4cee8","9183e064.d8367","e1d8f4ed.be4978"]]},{"id":"616a2c47.55eef4","type":"ui_button","z":"e98d7f.572f328","name":"","group":"f49cf745.77c7e8","order":5,"width":"2","height":"1","passthru":false,"label":"-1W","color":"","bgcolor":"","icon":"chevron_left","payload":"","payloadType":"str","topic":"minus1w","x":121.25,"y":568.75,"wires":[["85036109.e3fc9","71308322.8eb3bc"]]},{"id":"1774b24.f26074e","type":"ui_button","z":"e98d7f.572f328","name":"","group":"f49cf745.77c7e8","order":6,"width":"2","height":"1","passthru":false,"label":"-1D","color":"","bgcolor":"","icon":"chevron_left","payload":"","payloadType":"str","topic":"minus1d","x":119.25,"y":605.75,"wires":[["85036109.e3fc9","71308322.8eb3bc"]]},{"id":"15c1a717.290069","type":"ui_button","z":"e98d7f.572f328","name":"","group":"f49cf745.77c7e8","order":8,"width":"2","height":"1","passthru":false,"label":"+1W","color":"","bgcolor":"","icon":"chevron_right","payload":"","payloadType":"str","topic":"plus1w","x":122.25,"y":643.75,"wires":[["85036109.e3fc9","71308322.8eb3bc"]]},{"id":"1e4d821c.e6100e","type":"ui_button","z":"e98d7f.572f328","name":"","group":"f49cf745.77c7e8","order":7,"width":"2","height":"1","passthru":false,"label":"+1D","color":"","bgcolor":"","icon":"chevron_right","payload":"","payloadType":"str","topic":"plus1d","x":120.25,"y":680.75,"wires":[["85036109.e3fc9","71308322.8eb3bc"]]},{"id":"85036109.e3fc9","type":"change","z":"e98d7f.572f328","name":"Reset","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":349.25,"y":635.75,"wires":[["9183e064.d8367"]]},{"id":"e1d8f4ed.be4978","type":"ui_dropdown","z":"e98d7f.572f328","name":"Aggregate source","label":"","place":"","group":"f49cf745.77c7e8","order":2,"width":"5","height":"1","passthru":false,"options":[{"label":"[Remove all]","value":"delete","type":"str"},{"label":"Laser 1","value":"Chasis/Laser 1","type":"str"},{"label":"Laser 2","value":"Chasis/Laser 2","type":"str"}],"payload":"","topic":"aggrsource","x":579.25,"y":550.75,"wires":[["71308322.8eb3bc"]]},{"id":"42416ea5.ebdfe","type":"change","z":"e98d7f.572f328","name":"Title","rules":[{"t":"set","p":"payload","pt":"msg","to":"title","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":869.5000152587891,"y":732.0000095367432,"wires":[["d479010b.4e042"]]},{"id":"d479010b.4e042","type":"ui_text","z":"e98d7f.572f328","group":"f49cf745.77c7e8","order":1,"width":"0","height":"0","name":"Chart title","label":"","format":"{{msg.payload}}","layout":"row-center","x":1021.5000152587891,"y":732.0000095367432,"wires":[]},{"id":"7778e462.3b95ac","type":"ui_group","z":"","name":"Assembly Lines","tab":"6ed57066.2c961","disp":true,"width":"5","collapse":false},{"id":"4d41bb29.606284","type":"sqlitedb","z":"","db":"/var/www/html/database/Test","mode":"RWC"},{"id":"fe628096.4632f","type":"sqlitedb","z":"","db":"/var/www/html/database/testhumid","mode":"RWC"},{"id":"4f589ff5.47b02","type":"sqlitedb","z":"","db":"/var/www/html/database/nodered","mode":"RWC"},{"id":"f49cf745.77c7e8","type":"ui_group","z":"","name":"Report","tab":"6ed57066.2c961","order":2,"disp":false,"width":"22","collapse":false},{"id":"6ed57066.2c961","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"}]

Untitled
Untitled1142×402 51.5 KB

12

Put a debug node on the output of the function labeled SQL and see what shows up. Make sure to set it to display the complete msg object

13

Put a debug node on the output of the SQL node and show us what it is generating.

14

Here is the debug result

21.11.2018 13:28:49
[node: 1f0104be.0e649b](http://53.65.174.124:1880/#)
SELECT Value FROM Test : msg : Object

object

topic: "SELECT Value FROM Test"

complete: true

title: "No data source"

_msgid: "b34d8df4.b3408"




21.11.2018 13:28:51
[node: 1f0104be.0e649b](http://53.65.174.124:1880/#)
SELECT Value FROM Test : msg : Object

object

topic: "SELECT Value FROM Test"

complete: true

title: "No data source"

_msgid: "fa54d0a8.21225"
15

It would have been helpful if you had told us whether that is what you expected. Is it what you expected?
If not then what do you expect?
If it is then what do you see coming out of the db node and again is that what you expect?
If it is the what you do you see coming out of the join node and is that what you expect?
You get the idea by now I hope.

16

Is this the debug node on the function node or the sql node?

If it is the debug node:

  1. what did you do/push to get this to run?
  2. is that the result you expected - i.e. two messages coming out of the function node?
17

This is debug for function node. Just I push specific time , today and last 30 days.
2 message received beause I pushed 2 times.

I draw and showed what I expected in picture

Untitled
Untitled1492×673 43.1 KB

18

I meant is that what you expect from the function node going into the db node. You can't expect to get the right information onto the chart if you are not getting the right information for the database and you won't get the right information from the database unless you pass it the right query. So first make sure you get the query right.
For a start you are only asking for the Value, but if you want to draw a chart then I would have thought you would also need the timestamp.

19

You right. I didnt find and get right code for db query. I tried lots of combinations but did not worked. I need some code help.

20

Do you mean that you don't know what query to use to get the data you want from the database? If so that doesn't seem to be node-red related. This looks like a good place to start: