Hi,
I have tried to input two dates using two date picker and moment nodes and tried to retrieve the data from a sqlite database based on the input dates.
Database has four columns
1.timestamp - data type (Time),
2. sensor - data type (Text)
3. temperature-data type (Int)
4. humidity - data type (Int)
I wanted to get the maximum temperature of a column (temperature) where the related timestamp is between the entered dates through dashboard.
But I'm not getting the desired result.
[{"id":"ccb502ab.8b7c","type":"tab","label":"Flow 7","disabled":false,"info":""},{"id":"81091193.c336b","type":"ui_date_picker","z":"ccb502ab.8b7c","name":"Startdate","label":"Startdate","group":"cf5a7640.485f38","order":4,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","className":"","x":260,"y":700,"wires":[["d6093f4b.93ef7"]]},{"id":"cb8ca93f.8091d8","type":"ui_date_picker","z":"ccb502ab.8b7c","name":"Enddate","label":"Enddate","group":"cf5a7640.485f38","order":5,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","className":"","x":260,"y":800,"wires":[["4ab82caf.30b024"]]},{"id":"d6093f4b.93ef7","type":"moment","z":"ccb502ab.8b7c","name":"stday","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Colombo","adjAmount":0,"adjType":"hours","adjDir":"add","format":"MM/DD/YYYY, HH:mm:ss A","locale":"en_US","output":"stday","outputType":"global","outTz":"Asia/Colombo","x":430,"y":700,"wires":[["8928cb0c.819d38"]]},{"id":"4ab82caf.30b024","type":"moment","z":"ccb502ab.8b7c","name":"enday","topic":"","input":"payload","inputType":"msg","inTz":"Asia/Colombo","adjAmount":0,"adjType":"days","adjDir":"add","format":"MM/DD/YYYY, HH:mm:ss A","locale":"en_US","output":"enday","outputType":"global","outTz":"Asia/Colombo","x":420,"y":800,"wires":[["8928cb0c.819d38"]]},{"id":"8928cb0c.819d38","type":"function","z":"ccb502ab.8b7c","name":"","func":"var stday1 = global.get(\"stday\") ||0 ;\nvar enday1 = global.get(\"enday\") ||0 ;\nvar stdaystring = stday1.toLocaleString();\nvar endaystring = enday1.toLocaleString();\n//msg.payload = d.toISOString();\n//return msg;\n//var dateObject = new Date('\"+stday1+\"');\n//var datestring = dateObject.toLocaleString();\n//return dateObject;\n/*var newmsg;\nnewmsg = {\n \"topic\" : \"SELECT max(humidity) from data WHERE timestamp between '\"+stday1+\"' AND '\"+enday1+\"'\"\n};\nreturn newmsg;*/\n//var b = \"SELECT max(TEMPERATURE) from data WHERE TIMESTAMP between \"+stday1+\" AND \"+enday1+\"\";\n//msg.topic=b;\n//return msg;\n//msg.topic = \"SELECT max(temperature) FROM data WHERE timestamp BETWEEN '\" + startDate + \" \" + startTime + \" 'AND '\" + endDate + \" \" + endTime + \"' ORDER BY timestamp limit 5000\";\n/*if (stday1 && enday1 &&\n enday1 >= stday1) {\n msg.topic = \"SELECT max(temperature) FROM data WHERE timestamp BETWEEN '\"+stday1+\"' AND '\"+enday1+\"' ORDER BY timestamp limit 5000\";\n return msg;\n } else {\n node.warn(\"end date is before start date. or context not set\");\n }*/\n if (stday1 && enday1 &&\n enday1 >= stday1) {\n msg.topic = \"SELECT min(temperature) FROM data WHERE timestamp BETWEEN '\" + stdaystring + \"' AND '\" + endaystring + \"' ORDER BY timestamp limit 5000\";\n return msg;\n } else {\n node.warn(\"end date is before start date. or context not set\");\n }\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":740,"wires":[["7c2db59c.aa999c"]]},{"id":"7c2db59c.aa999c","type":"sqlite","z":"ccb502ab.8b7c","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":800,"y":740,"wires":[["b3fd5f24.c24b6"]]},{"id":"b3fd5f24.c24b6","type":"debug","z":"ccb502ab.8b7c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":990,"y":740,"wires":[]},{"id":"fe2b69a7.d6e0c8","type":"inject","z":"ccb502ab.8b7c","name":"create database table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)","payload":"","payloadType":"date","x":340,"y":80,"wires":[["7fe9b044.cee89"]]},{"id":"5d8554c3.c60f3c","type":"function","z":"ccb502ab.8b7c","name":"Insert","func":"var date=new Date().toLocaleString();\nvar newMsg = {\n \"topic\": \"INSERT INTO data ( timestamp,sensor,temperature,humidity) values('\" + date + \"',50,80,66)\"\n};\n\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":140,"wires":[["7fe9b044.cee89","d223cf5b.31a35"]]},{"id":"4ba348.cb541cb8","type":"inject","z":"ccb502ab.8b7c","name":"delete records","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":230,"y":120,"wires":[[]]},{"id":"7fe9b044.cee89","type":"sqlite","z":"ccb502ab.8b7c","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":540,"y":240,"wires":[["3e8d6e19.e86e72"]]},{"id":"d223cf5b.31a35","type":"debug","z":"ccb502ab.8b7c","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":730,"y":120,"wires":[]},{"id":"3deba921.051626","type":"inject","z":"ccb502ab.8b7c","name":"Drop Table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":320,"y":180,"wires":[["7fe9b044.cee89"]]},{"id":"ff5a3c1.6b6efc","type":"inject","z":"ccb502ab.8b7c","name":"insert","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":270,"y":240,"wires":[["5d8554c3.c60f3c"]]},{"id":"3e8d6e19.e86e72","type":"debug","z":"ccb502ab.8b7c","name":"database_out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":880,"y":260,"wires":[]},{"id":"e5ff6da9.29721","type":"ui_button","z":"ccb502ab.8b7c","name":"Delete","group":"cf5a7640.485f38","order":1,"width":0,"height":0,"passthru":false,"label":"Delete","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"date","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","topicType":"str","x":370,"y":120,"wires":[["7fe9b044.cee89"]]},{"id":"8ec38aab.d505a8","type":"inject","z":"ccb502ab.8b7c","name":"show RECORDS","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":270,"y":340,"wires":[["49592d07.044c84"]]},{"id":"44bc105f.bcc9b","type":"ui_template","z":"ccb502ab.8b7c","group":"d44d4f44.3219e8","name":"UI Table","order":1,"width":"","height":"","format":"<style>\n.table\n{\n height:400px;\n width:550px;\n background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n <tr>\n <th>TIMESTAMP</th> \n <th>SENSOR</th>\n <th>TEMPERATURE</th> \n <th>HUMIDITY</th>\n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].SENSOR}}</td>\n <td>{{msg.payload[$index].TEMPERATURE}}</td> \n <td>{{msg.payload[$index].HUMIDITY}}</td>\n\n </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":800,"y":360,"wires":[["2f912045.b8a6d"]]},{"id":"49592d07.044c84","type":"sqlite","z":"ccb502ab.8b7c","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":480,"y":360,"wires":[["44bc105f.bcc9b"]]},{"id":"2f912045.b8a6d","type":"change","z":"ccb502ab.8b7c","name":"","rules":[{"t":"set","p":"mydata","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":920,"y":420,"wires":[[]]},{"id":"4f381854.9114d8","type":"inject","z":"ccb502ab.8b7c","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"mydata","payloadType":"flow","x":650,"y":420,"wires":[["44bc105f.bcc9b"]]},{"id":"41422ca.76410d4","type":"ui_button","z":"ccb502ab.8b7c","name":"","group":"62112e23.8d599","order":1,"width":0,"height":0,"passthru":false,"label":"Click here","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"date","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","topicType":"str","x":270,"y":420,"wires":[["49592d07.044c84"]]},{"id":"cf5a7640.485f38","type":"ui_group","name":"Sensors","tab":"e72a0b6e.b277a8","order":1,"disp":true,"width":"12","collapse":true},{"id":"9f3c85e2.f9a5d8","type":"sqlitedb","db":"C:\\sqlite\\newtest.db","mode":"RWC"},{"id":"d44d4f44.3219e8","type":"ui_group","name":"Sensors","tab":"34054411.bf9404","order":1,"disp":true,"width":"12","collapse":false},{"id":"62112e23.8d599","type":"ui_group","name":"Show Records","tab":"e72a0b6e.b277a8","order":2,"disp":true,"width":"6","collapse":true},{"id":"e72a0b6e.b277a8","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false},{"id":"34054411.bf9404","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
Could anyone kindly guide me on this?
Thank you
