Hi,
I'm trying to get the time from date picker and time input nodes and then map that timestamp to my database and retrieve a maximum value. Here I have used a format published in this forum before.
I'm getting the time inputs but I can't convert it to local time zone (UTC + 05.30 H) as the times are displayed in UTC time.
flows (1).json (4.3 KB)
This is my flow
[{"id":"d2556121.4d5c1","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"1ec0d27a.f64fae","type":"ui_date_picker","z":"d2556121.4d5c1","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"startDate","topicType":"msg","className":"","x":400,"y":120,"wires":[["f6da907d.19b36"]]},{"id":"f6da907d.19b36","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"$moment($$.payload).format("DD-MM-YYYY")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":120,"wires":[]},{"id":"5fb3276f.f26f88","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"$moment($$.payload).format("hh:mm:ss A")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":160,"wires":[]},{"id":"fe1ffcae.ee1e3","type":"ui_text_input","z":"d2556121.4d5c1","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"startTime","sendOnBlur":true,"className":"","topicType":"msg","x":410,"y":160,"wires":[["5fb3276f.f26f88"]]},{"id":"d35670e6.22a12","type":"ui_date_picker","z":"d2556121.4d5c1","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"endDate","topicType":"msg","className":"","x":300,"y":220,"wires":[["ab1cc480.225378"]]},{"id":"47ac46d6.840bc8","type":"ui_text_input","z":"d2556121.4d5c1","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"endTime","sendOnBlur":true,"className":"","topicType":"msg","x":300,"y":260,"wires":[["c6953e54.84ffd"]]},{"id":"ab1cc480.225378","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"$moment($$.payload).format("DD-MM-YYYY")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":220,"wires":[]},{"id":"c6953e54.84ffd","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"$moment($$.payload).format("hh:mm:ss A")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":260,"wires":[]},{"id":"68fc2e0b.82c97","type":"inject","z":"d2556121.4d5c1","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"query","payload":"","payloadType":"date","x":230,"y":340,"wires":[["375c6c15.d9c7e4"]]},{"id":"6e27c3b8.745b3c","type":"debug","z":"d2556121.4d5c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":360,"wires":},{"id":"e0833d6b.fcdbf","type":"sqlite","z":"d2556121.4d5c1","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":630,"y":500,"wires":[["6e27c3b8.745b3c"]]},{"id":"375c6c15.d9c7e4","type":"function","z":"d2556121.4d5c1","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == "download" || msg.topic == "query"){\n\n var startDate = flow.get("startDate");\n var startTime = flow.get("startTime") || false;\n \n var endDate = flow.get("endDate") || false;\n var endTime = flow.get("endTime") || false;\n\n // check if date range is correct\n if (startTime && startTime && endDate && endTime &&\n new Date(endDate) >= new Date(startDate)) {\n msg.topic = "SELECT max(temperature) FROM data WHERE timestamp BETWEEN '" + startDate + " " + startTime + " 'AND '" + endDate + " " + endTime + "' 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\n}\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":,"x":400,"y":400,"wires":[["e0833d6b.fcdbf"]]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":"17","collapse":false,"className":""},{"id":"9f3c85e2.f9a5d8","type":"sqlitedb","db":"C:\sqlite\newtest.db","mode":"RWC"},{"id":"48313a88.497314","type":"ui_tab","name":"monitoring","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]
Any help regarding this will be highly appreciated.