How To Make Logging Data Values in Chart

General
21

Interesting because when I select Miflora temperature I see two debugs only selecting one thing

11/21/2018, 11:25:15 AMnode: fa0129cc.c14c6
SELECT * FROM Test WHERE device='miflora' AND sensor='temp' AND epoch >= 1542731115534 AND epoch <= 1542817515534 : msg.payload : undefined
undefined
11/21/2018, 11:25:15 AMnode: fa0129cc.c14c6
SELECT * FROM Test WHERE device='miflora' AND sensor='temp' AND epoch >= 1542731115534 AND epoch <= 1542817515534 : msg.payload : undefined
undefined

Are you sure the last flow you pasted in is the correct one?

22

Sorry this is my current flow,

[{"id":"46ff0d77.645284","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"40","intype":"tri","debounce":"25","read":false,"x":190,"y":80,"wires":[["22588430.0e35ac"]]},{"id":"22588430.0e35ac","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 1';\"\n    }\n}","x":376,"y":78,"wires":[["1848bc32.816934","6265eee1.7febd"]]},{"id":"1848bc32.816934","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 1","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":80,"wires":[[]]},{"id":"6265eee1.7febd","type":"function","z":"6b9244e9.38681c","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":820,"y":200,"wires":[["37c1dd9a.906522"]]},{"id":"37c1dd9a.906522","type":"sqlite","z":"6b9244e9.38681c","mydb":"f3c1cd16.6ca33","sqlquery":"msg.topic","sql":"","name":"Record","x":1000,"y":180,"wires":[[]]},{"id":"67f1c2df.86930c","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"38","intype":"tri","debounce":"25","read":false,"x":190,"y":120,"wires":[["373a800f.5b7fc"]]},{"id":"373a800f.5b7fc","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 2';\"\n    }\n}","x":376,"y":118,"wires":[["6265eee1.7febd","712f6c17.7e3244"]]},{"id":"16a37a49.0f75e6","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"36","intype":"tri","debounce":"25","read":false,"x":190,"y":160,"wires":[["89de5007.c365a"]]},{"id":"89de5007.c365a","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 3';\"\n    }\n}","x":376,"y":158,"wires":[["6265eee1.7febd","bcd83f58.47d85"]]},{"id":"9351ffbb.7d163","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"35","intype":"tri","debounce":"25","read":false,"x":190,"y":200,"wires":[["39a0b1e1.86a0fe"]]},{"id":"39a0b1e1.86a0fe","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 4';\"\n    }\n}","x":376,"y":198,"wires":[["6265eee1.7febd","4a7b03f4.e205cc"]]},{"id":"3c79b32a.80df3c","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"33","intype":"tri","debounce":"25","read":false,"x":190,"y":240,"wires":[["9a8d4f3b.16661"]]},{"id":"9a8d4f3b.16661","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 5';\"\n    }\n}","x":376,"y":238,"wires":[["6265eee1.7febd","62f65d85.16b984"]]},{"id":"28ff9497.d370bc","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"32","intype":"tri","debounce":"25","read":false,"x":190,"y":280,"wires":[["e37a1668.a832b8"]]},{"id":"e37a1668.a832b8","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 6';\"\n    }\n}","x":376,"y":278,"wires":[["6265eee1.7febd","559a41c.2f942c"]]},{"id":"712f6c17.7e3244","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 2","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":120,"wires":[[]]},{"id":"bcd83f58.47d85","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 3","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":160,"wires":[[]]},{"id":"4a7b03f4.e205cc","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 4","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":200,"wires":[[]]},{"id":"62f65d85.16b984","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 5","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":240,"wires":[[]]},{"id":"559a41c.2f942c","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 6","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":280,"wires":[[]]},{"id":"19d32b38.da06a5","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"31","intype":"tri","debounce":"25","read":false,"x":190,"y":320,"wires":[["36a1d854.37ab88"]]},{"id":"36a1d854.37ab88","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 7';\"\n    }\n}","x":376,"y":318,"wires":[["b7ff37ad.9be578","6265eee1.7febd"]]},{"id":"b7ff37ad.9be578","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 7","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":320,"wires":[[]]},{"id":"74223fda.50e06","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"29","intype":"tri","debounce":"25","read":false,"x":190,"y":360,"wires":[["d9e521b6.9db3e"]]},{"id":"d9e521b6.9db3e","type":"dsm","z":"6b9244e9.38681c","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';\",\n        \"1\": \"sta.fill='red';msg.background='red';msg.label='Laser 8';\"\n    }\n}","x":370,"y":360,"wires":[["fc43d75d.237968","6265eee1.7febd"]]},{"id":"fc43d75d.237968","type":"ui_button","z":"6b9244e9.38681c","name":"Laser 8","group":"ab3c17d5.a29c88","order":0,"width":0,"height":0,"passthru":false,"label":"{{msg.label}}","color":"","bgcolor":"{{msg.background}}","icon":"","payload":"","payloadType":"str","topic":"","x":560,"y":360,"wires":[[]]},{"id":"89fcb50c.b4af58","type":"rpi-dht22","z":"6b9244e9.38681c","name":"","topic":"Dht11","dht":"11","pintype":"0","pin":4,"x":390,"y":500,"wires":[["804a0484.c57298","79077a3e.2f34f4"]]},{"id":"4ae49d6d.7f3614","type":"rpi-gpio in","z":"6b9244e9.38681c","name":"","pin":"7","intype":"tri","debounce":"25","read":false,"x":180,"y":526.0000171661377,"wires":[["89fcb50c.b4af58"]]},{"id":"804a0484.c57298","type":"function","z":"6b9244e9.38681c","name":"Sicaklik","func":"msg.payload = msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":560,"y":480,"wires":[["170a5443.70c6dc","456d30ce.a7df5"]]},{"id":"170a5443.70c6dc","type":"ui_gauge","z":"6b9244e9.38681c","name":"","group":"ab3c17d5.a29c88","order":0,"width":"4","height":"4","gtype":"gage","title":"Cabinet Temp","label":"°C","format":"{{value}}","min":0,"max":"40","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":730,"y":460,"wires":[]},{"id":"79077a3e.2f34f4","type":"function","z":"6b9244e9.38681c","name":"Nem","func":"msg.payload = msg.humidity;\nreturn msg;","outputs":1,"noerr":0,"x":570,"y":520,"wires":[["55ef4e95.87499","456d30ce.a7df5","810274d5.392718"]]},{"id":"456d30ce.a7df5","type":"function","z":"6b9244e9.38681c","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":880,"y":480,"wires":[["1ff6643a.fb013c"]]},{"id":"569ff718.170e58","type":"inject","z":"6b9244e9.38681c","name":"","topic":"","payload":"","payloadType":"date","repeat":"30","crontab":"","once":false,"onceDelay":0.1,"x":204.60765075683594,"y":457.94922828674316,"wires":[["89fcb50c.b4af58"]]},{"id":"55ef4e95.87499","type":"ui_gauge","z":"6b9244e9.38681c","name":"","group":"ab3c17d5.a29c88","order":0,"width":"4","height":"4","gtype":"gage","title":"Cabinet Humid","label":"%","format":"{{value}}","min":0,"max":"60","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":720,"y":520,"wires":[]},{"id":"1ff6643a.fb013c","type":"sqlite","z":"6b9244e9.38681c","mydb":"7bdbd364.b646bc","sqlquery":"msg.topic","sql":"","name":"Temp-Humid","x":1050,"y":460,"wires":[[]]},{"id":"810274d5.392718","type":"ui_chart","z":"6b9244e9.38681c","name":"","group":"c8339265.6279f","order":0,"width":"0","height":"0","label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1010,"y":520,"wires":[[],[]]},{"id":"66136748.556da8","type":"function","z":"6b9244e9.38681c","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 = [];\nvar 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}\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  \n   }\n\n\n// Regenerate the SQL statements\n// Run through the data source list an generate the SQL statements\nsql = [];\n\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\" });\n    }\n} \nif (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\" });\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 ) {\n    title = \"No data source\";\n} \nelse \n    if (sourcelist.length!==0) {\n        title = title + sourcelist.toString()+ \", \";\n    }\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","outputs":1,"noerr":0,"x":350,"y":780,"wires":[["5973590c.35e1f8","f7bc523d.ca13a"]]},{"id":"5973590c.35e1f8","type":"sqlite","z":"6b9244e9.38681c","mydb":"853d71fa.53f76","sql":"","name":"DB","x":490,"y":780,"wires":[["d152b562.6dd238"]]},{"id":"18920642.deb07a","type":"ui_chart","z":"6b9244e9.38681c","name":"Chart","group":"c81922a7.44887","order":2,"width":"17","height":"15","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":985.6666946411133,"y":819.2499485015869,"wires":[[],[]]},{"id":"165041eb.31a65e","type":"function","z":"6b9244e9.38681c","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].Value]);\n        }\n        msg2.push({ key: msg.payload[i][0].Value, 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":812.8334121704102,"y":819.5833225250244,"wires":[["18920642.deb07a"]]},{"id":"7f77ed08.60cff4","type":"inject","z":"6b9244e9.38681c","name":"Reset chart","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":510.74999237060547,"y":877.0000095367432,"wires":[["9a3f235f.89677"]]},{"id":"9a3f235f.89677","type":"function","z":"6b9244e9.38681c","name":"Empty payload","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"x":758.6666641235352,"y":876.3333234786987,"wires":[["18920642.deb07a"]]},{"id":"d152b562.6dd238","type":"join","z":"6b9244e9.38681c","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":623,"y":780,"wires":[["1779148f.ba408b","165041eb.31a65e"]]},{"id":"c20ddaf0.8f3678","type":"ui_dropdown","z":"6b9244e9.38681c","name":"Data source","label":"","place":"Choose Device","group":"c81922a7.44887","order":1,"width":"5","height":"1","passthru":false,"options":[{"label":"[Clear all]","value":"delete","type":"str"},{"label":"Laser 1","value":"Chasis/laser 1","type":"str"},{"label":"Laser 2","value":"Chasis/laser 2","type":"str"},{"label":"Laser 3","value":"Chasis/laser 3","type":"str"},{"label":"Laser 4","value":"Chasis/laser 4","type":"str"},{"label":"Laser 5","value":"Chasis/laser 5","type":"str"},{"label":"Laser 6","value":"Chasis/laser 6","type":"str"},{"label":"Laser 7","value":"Chasis/laser 7","type":"str"},{"label":"Laser 8","value":"Chasis/laser 8","type":"str"}],"payload":"","topic":"datasource","x":420.25,"y":632.75,"wires":[["66136748.556da8"]]},{"id":"1bdbfca3.3ebef3","type":"ui_dropdown","z":"6b9244e9.38681c","name":"Period","label":"","place":"Choose Time","group":"c81922a7.44887","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":399.25,"y":675.75,"wires":[["66136748.556da8"]]},{"id":"d57c9f1f.62b6c","type":"inject","z":"6b9244e9.38681c","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":true,"x":211.25,"y":615.75,"wires":[["c20ddaf0.8f3678","1bdbfca3.3ebef3"]]},{"id":"26702ea5.6f6192","type":"change","z":"6b9244e9.38681c","name":"Reset","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":209.25,"y":675.75,"wires":[["1bdbfca3.3ebef3"]]},{"id":"1779148f.ba408b","type":"change","z":"6b9244e9.38681c","name":"Title","rules":[{"t":"set","p":"payload","pt":"msg","to":"title","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":829.5000152587891,"y":732.0000095367432,"wires":[["b77cb058.3e85a"]]},{"id":"b77cb058.3e85a","type":"ui_text","z":"6b9244e9.38681c","group":"c81922a7.44887","order":1,"width":"0","height":"0","name":"Chart title","label":"","format":"{{msg.payload}}","layout":"row-center","x":981.5000152587891,"y":732.0000095367432,"wires":[]},{"id":"f7bc523d.ca13a","type":"debug","z":"6b9244e9.38681c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":530,"y":720,"wires":[]},{"id":"ab3c17d5.a29c88","type":"ui_group","z":"","name":"Assembly Lines","tab":"6ed57066.2c961","disp":true,"width":"5","collapse":false},{"id":"f3c1cd16.6ca33","type":"sqlitedb","z":"","db":"/var/www/html/database/Test","mode":"RWC"},{"id":"7bdbd364.b646bc","type":"sqlitedb","z":"","db":"/var/www/html/database/testhumid","mode":"RWC"},{"id":"c8339265.6279f","type":"ui_group","z":"","name":"Tarik","tab":"33f6fad9.60c266","disp":true,"width":"20","collapse":false},{"id":"853d71fa.53f76","type":"sqlitedb","z":"","db":"/var/www/html/database/Test","mode":"RWC"},{"id":"c81922a7.44887","type":"ui_group","z":"","name":"Report","tab":"6ed57066.2c961","order":2,"disp":false,"width":"17","collapse":false},{"id":"6ed57066.2c961","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"},{"id":"33f6fad9.60c266","type":"ui_tab","z":"","name":"Denememeeeee","icon":"dashboard"}]
23

With that flow what are you seeing in the debug node on the output of the function node?
Also what do you see on the output of the db node for that query.

24

When I choose for example Laser 4 and last month, graphic is not showing value , its empty. But there are some value in db

This is debug results.

SELECT Value FROM Test : msg : Object
object
topic: "SELECT Value FROM Test"
complete: true
_msgid: "abda0b08.c22348"


SELECT Value FROM Test : msg : Object
object
topic: "SELECT Value FROM Test"
complete: true
title: "Chasis/laser 4 | 23.10.2018 - 22.11.2018"

_msgid: "6a41d49c.d877dc"

Untitled
Untitled.png1742×967 81.7 KB

25

Please try and answer all the questions, you have not shown us what is coming out of the database node.
Also I have to ask again, do you believe that the query you just posted (Select Value from Test) is going to provide the correct data for the chart you want?

26

Of course this query is not enough.

But if I knew what query is working, I did not create a topic?
Last 4 days Im trying lots of code and didnt success it.

What is your other question ?

27

put a debug node on the output of the SQL node AND on the output of the Join node. Do you get data from both?

28

After put join node debug the result is this.

SQL Data Debug
{"topic":"SELECT Value FROM Test","complete":true,"title":"No data sour | 23.10.2018 - 22.11.2018","_msgid":"119c714d.3b230f"}

Join node debug
No data sour | 23.10.2018 - 22.11.2018
{"topic":"SELECT Value FROM Test","title":"No data sour | 23.10.2018 - 22.11.2018","_msgid":"119c714d.3b230f","payload":[[{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0}]]}
29

So you ARE getting data from the join node (look at the msg.payload)
{"topic":"SELECT Value FROM Test","title":"No data sour | 23.10.2018 - 22.11.2018","_msgid":"119c714d.3b230f","payload":[[{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0},{"Value":1},{"Value":0}]]}
Looks like you need to figure out how to formate it for the chart

30

OK, you know the query is not what you want. I presume therefore that you still don't know what query you want. So do you agree that the problem you have is that you don't know enough about sql to know what query to use?

31

I looked what you suggested link yesterday and before, but there are not enough information.
I found SELECT * DROM db
just this, and nothing I success.

32

At the first point yes, but when I choose todayor yesterday or last month or whatever I have, It shows all records.
How can I go forward now, anyway for solution ?

33

Then your query is wrong. You should look at the query you are generating (this is why I had you put the debug nodes in).

Have you taken an SQL tutorial?

34

Yes, last 4 days I tried lots of code but didnt worked.Something didnt write tutorial.

35

So what data do you want to come from the SQL query?
What is the WHERE clause you need?

36

I want just value 1 or 0. I dont need WHERE clause.

37

if you read the Info tab for the SQL node you will see:

Using any SQL Query, the result is returned in msg.payload

Typically the returned payload will be an array of the result rows, (or an error). Using any SQL Query, the result is returned in msg.payload

Which is exactly what you are receiving. An array where each occurance contains the name of the column and the value for that column and each element in the array is one of the rows returned.

So if you just want 1's and 0's you will have to process the msg.payload to access them. You might want to look at the split node

38

You need the where clause to specify which period you want the data for, and if you want to put it on a chart don't you need to know the time of each sample?

39

Hi again.
Lots of days Im trying to make visualizing my any data for example dht11 temp and humidty or other not important what is it.

I found an example in flows side " mysql db visualizing data" .
I did same flow, created db and some value but dont see values in chart. Where I made a mistake. When I inject there are no errors but values didnt create a chart data.
I share my node here.
Please help me.

[{"id":"d66f40a1.07d79","type":"function","z":"4bb3915c.94439","name":"Criteria","func":"var timeE = msg.payload;\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nmsg.payload = (timeE - (1000*60*60*24));\n    node.status({text:msg.payload});\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":327,"wires":[["de98ee8a.e0e78"]]},{"id":"6b580a35.e0c684","type":"debug","z":"4bb3915c.94439","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":782,"y":331,"wires":[]},{"id":"7f1b14c3.41efac","type":"inject","z":"4bb3915c.94439","name":"Timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":132,"y":329,"wires":[["d66f40a1.07d79"]]},{"id":"23e04a90.dfd4b6","type":"template","z":"4bb3915c.94439","name":"Format data","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO `mydbase` (`data1`,`data2`,`time`) VALUES ({{data1}},{{data2}},{{time}})","output":"str","x":276,"y":157,"wires":[["4a385a1b.5390c4","f1bc54b2.513988"]]},{"id":"de98ee8a.e0e78","type":"template","z":"4bb3915c.94439","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT data1,data2,time FROM th WHERE time > {{payload}}","output":"str","x":443,"y":340,"wires":[["aab2d7ac.7a1a18"]]},{"id":"d8ad0916.c2bae8","type":"change","z":"4bb3915c.94439","name":"Format data","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t  $series := [\t    { \"field\": \"data1\", \"label\": \"data1\" },\t    { \"field\": \"data2\", \"label\": \"data2\" }\t    ];\t  $xaxis := \"timestamp\";\t  [\t    {\t      \"series\": $series.label,\t      \"data\": $series.[\t        (\t          $yaxis := $.field;\t          $$.payload.{\t            \"x\": $lookup($, $xaxis),\t            \"y\": $lookup($, $yaxis)\t          }\t        )\t      ]\t    }\t  ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":805,"y":282,"wires":[["1cc38dda.c1f642"]]},{"id":"4a385a1b.5390c4","type":"sqlite","z":"4bb3915c.94439","mydb":"4e79b940.206568","sqlquery":"msg.topic","sql":"","name":"save","x":445.5,"y":162,"wires":[[]]},{"id":"aab2d7ac.7a1a18","type":"sqlite","z":"4bb3915c.94439","mydb":"4e79b940.206568","sqlquery":"msg.topic","sql":"","name":"sendGraph","x":630.5,"y":318,"wires":[["6b580a35.e0c684","d8ad0916.c2bae8"]]},{"id":"386bb478.061f6c","type":"inject","z":"4bb3915c.94439","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":157,"wires":[["23e04a90.dfd4b6"]]},{"id":"f1bc54b2.513988","type":"debug","z":"4bb3915c.94439","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":473,"y":63,"wires":[]},{"id":"1cc38dda.c1f642","type":"ui_chart","z":"4bb3915c.94439","name":"","group":"4459cf06.2cbf8","order":0,"width":0,"height":0,"label":"Graphic","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"24","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":971,"y":322,"wires":[[],[]]},{"id":"4e79b940.206568","type":"sqlitedb","z":"","db":"/var/www/html/dbase/mydbase","mode":"RWC"},{"id":"4459cf06.2cbf8","type":"ui_group","z":"","name":"Aksaray","tab":"929b4fa0.44b05","disp":true,"width":"20","collapse":false},{"id":"929b4fa0.44b05","type":"ui_tab","z":"","name":"Deneme","icon":"dashboard"}]
40

So you have an insert into the database that is inserting two data values and the current time into the database.

  1. i see where the time is cominng from - the inject node, but where is the data coming from?

next you create a select and run it but you are not showing the results of the debug node connected to the 'SendGraph' sqlite node.