Retrieve Data from sqlite database based on the input time from date picker and moment nodes

Dashboard
1

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

2

Change yout tab to this:

<style>
.table
{
    height:400px;
    width:550px;
    background:lightblue;
}
</style>
<div class="table">
<table style="width:100%">
  <tr>
    <th>TIMESTAMP</th> 
    <th>SENSOR</th>
    <th>TEMPERATURE</th> 
    <th>HUMIDITY</th>
  
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:10">
    <td>{{x.TIMESTAMP}}</td>
    <td>{{x.SENSOR}}</td>
    <td>{{x.TEMPERATURE}}</td> 
    <td>{{x.HUMIDITY}}</td>

  </tr>
</table>
</div>

you also might want to move the table to the same group as the input group. You have creatd two 'Home' groups. edit the ui-table node and...

Screen Shot 2022-02-17 at 4.55.36 AM
Screen Shot 2022-02-17 at 4.55.36 AM699×471 47.6 KB

3

Thanks. But still the issue prevails. I have used this function to retrieve data

var stday1 = global.get('stday') ||0 ;
var enday1 = global.get('enday') ||0 ;
if (stday1 && enday1 &&
    enday1 >= stday1) {
        msg.topic = "SELECT min(temperature) FROM data WHERE timestamp >  '"+stday1+"' ORDER BY timestamp";
        return msg;
    } else {
        node.warn("end date is before start date. or context not set");
    }
4

let me see your current flow

5 
[{"id":"5b860526.bdd4ec","type":"function","z":"32075b67.0712e4","name":"","func":"var stday1 = global.get('stday') ||0 ;\nvar enday1 = global.get('enday') ||0 ;\nvar stdaystring = stday1.toLocaleString();\nvar endaystring = enday1.toLocaleString();\nvar a = new Date (stday1).toLocaleString();\n//var b = new Date (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 > '\"+stday1+\"' 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    //correct version is shown below\n  if (stday1 && enday1 &&\n    enday1 >= stday1) {\n        msg.topic = \"SELECT min(temperature) FROM data WHERE timestamp >  '\"+stday1+\"' ORDER BY timestamp\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date. or context not set\");\n    }\n    //test\n/* if (stday1 && enday1 &&\n    enday1 >= stday1) {\n        msg.topic = \"SELECT min(temperature) FROM data WHERE timestamp > '\"a\"' 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    //test\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":740,"wires":[["2913135c.9d3b2c"]]}]
6

That is just the function, not the entire flow.

Also, what is your

desired result.

7 
[{"id":"fd8d3d1d.7d2c3","type":"tab","label":"Flow 7","disabled":true,"info":""},{"id":"5b6ac125.98b79","type":"ui_date_picker","z":"fd8d3d1d.7d2c3","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":[["c36145b0.c66868"]]},{"id":"404f6dce.1135b4","type":"ui_date_picker","z":"fd8d3d1d.7d2c3","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":[["56bc11ab.ce34d"]]},{"id":"c36145b0.c66868","type":"moment","z":"fd8d3d1d.7d2c3","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":[["bbfff85c.89cd78"]]},{"id":"56bc11ab.ce34d","type":"moment","z":"fd8d3d1d.7d2c3","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":[["bbfff85c.89cd78"]]},{"id":"bbfff85c.89cd78","type":"function","z":"fd8d3d1d.7d2c3","name":"","func":"var stday1 = global.get('stday') ||0 ;\nvar enday1 = global.get('enday') ||0 ;\nvar stdaystring = stday1.toLocaleString();\nvar endaystring = enday1.toLocaleString();\nvar a = new Date (stday1).toLocaleString();\n//var b = new Date (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 > '\"+stday1+\"' 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    //correct version is shown below\n  if (stday1 && enday1 &&\n    enday1 >= stday1) {\n        msg.topic = \"SELECT min(temperature) FROM data WHERE timestamp >  '\"+stday1+\"' ORDER BY timestamp\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date. or context not set\");\n    }\n    //test\n/* if (stday1 && enday1 &&\n    enday1 >= stday1) {\n        msg.topic = \"SELECT min(temperature) FROM data WHERE timestamp > '\"a\"' 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    //test\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":610,"y":740,"wires":[["b86ce0aa.a692c"]]},{"id":"b86ce0aa.a692c","type":"sqlite","z":"fd8d3d1d.7d2c3","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":800,"y":740,"wires":[["703580a.197ed8"]]},{"id":"703580a.197ed8","type":"debug","z":"fd8d3d1d.7d2c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":990,"y":740,"wires":[]},{"id":"79aeb78e.08f278","type":"inject","z":"fd8d3d1d.7d2c3","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":[["e1ba703e.e4bb4"]]},{"id":"2469e4b6.63ff0c","type":"function","z":"fd8d3d1d.7d2c3","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":[["e1ba703e.e4bb4","dca047cf.795638"]]},{"id":"dab0d10f.14a8e","type":"inject","z":"fd8d3d1d.7d2c3","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":"e1ba703e.e4bb4","type":"sqlite","z":"fd8d3d1d.7d2c3","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":540,"y":240,"wires":[["443b61c5.b00e7"]]},{"id":"dca047cf.795638","type":"debug","z":"fd8d3d1d.7d2c3","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":730,"y":120,"wires":[]},{"id":"5f2c32c5.0d0adc","type":"inject","z":"fd8d3d1d.7d2c3","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":[["e1ba703e.e4bb4"]]},{"id":"f96d24f5.c8b328","type":"inject","z":"fd8d3d1d.7d2c3","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":[["2469e4b6.63ff0c"]]},{"id":"443b61c5.b00e7","type":"debug","z":"fd8d3d1d.7d2c3","name":"database_out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":880,"y":260,"wires":[]},{"id":"6069e1bb.1f8a8","type":"ui_button","z":"fd8d3d1d.7d2c3","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":[["e1ba703e.e4bb4"]]},{"id":"8e71e354.b8705","type":"inject","z":"fd8d3d1d.7d2c3","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":[["ae3aa34.298086"]]},{"id":"9d0872e6.8305d","type":"ui_template","z":"fd8d3d1d.7d2c3","group":"cf5a7640.485f38","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>{{x.TIMESTAMP}}</td>\n    <td>{{x.SENSOR}}</td>\n    <td>{{x.TEMPERATURE}}</td> \n    <td>{{x.HUMIDITY}}</td>\n\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":800,"y":360,"wires":[["77f9dbc0.ac97e4"]]},{"id":"ae3aa34.298086","type":"sqlite","z":"fd8d3d1d.7d2c3","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":480,"y":360,"wires":[["9d0872e6.8305d"]]},{"id":"77f9dbc0.ac97e4","type":"change","z":"fd8d3d1d.7d2c3","name":"","rules":[{"t":"set","p":"mydata","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":920,"y":420,"wires":[[]]},{"id":"75945611.a69e88","type":"inject","z":"fd8d3d1d.7d2c3","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":[["9d0872e6.8305d"]]},{"id":"8502a90f.594938","type":"ui_button","z":"fd8d3d1d.7d2c3","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":[["ae3aa34.298086"]]},{"id":"21feb3a3.ae6aec","type":"function","z":"fd8d3d1d.7d2c3","name":"","func":"var newMsg = {\n \"topic\": \"SELECT timestamp FROM data\"\n};\n\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":960,"wires":[["28edb918.faf5c6"]]},{"id":"28edb918.faf5c6","type":"sqlite","z":"fd8d3d1d.7d2c3","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":670,"y":960,"wires":[["277a725d.ab7e4e"]]},{"id":"277a725d.ab7e4e","type":"debug","z":"fd8d3d1d.7d2c3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1020,"y":960,"wires":[]},{"id":"63f32ed9.65d18","type":"inject","z":"fd8d3d1d.7d2c3","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":980,"wires":[["21feb3a3.ae6aec"]]},{"id":"d3f4732f.26277","type":"string","z":"fd8d3d1d.7d2c3","d":true,"name":"","methods":[{"name":"replaceAll","params":[{"type":"str","value":":"},{"type":"str","value":""}]}],"prop":"payload","propout":"payload","object":"msg","objectout":"msg","x":440,"y":280,"wires":[[]]},{"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":"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}]

Sorry for the inconvenience. For example, I want to enter start date as 10/02/2022 and get the minimum of temperature from the database where timestamp > 10/02/2022.

8

What are the values you have stored in the globals?

Screen Shot 2022-02-17 at 5.36.00 AM

To help debug your issue, you can add 'node.warn()statements in yourfunction` nodes. Try adding


node.warn("stday1=")
node.warn(stday1)
node.warn("enday1=")
node.warn(enday1)
node.warn("stdaystring=")
node.warn(stdaystring)
node.warn("endaystring=")
node.warn(endaystring)
node.warn("a=")
node.warn(a)

after you set the variables so you can see what the are.

as an aside, you should use INTEGER inplace of INT

9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.