Multiple msg.payloads with different topics combined and inserted into MySQL but data in table not shown

Dashboard
1

Hi,

I am creating a dashboard with temperature, humidity and heatindex gauge and history charts. The dashboard works fine for realtime data. I have inserted the 3 parameters data in Mysql by using join and function node. However i would not be able to read the data back from Mysql in dashboard. I have created a form with data wise search input but still cant get the data visualize in table or chart as historical data.

[{"id":"a79b8870bf169f3d","type":"tab","label":"Flow 15","disabled":false,"info":"","env":[]},{"id":"afee5c9df15d52a8","type":"mqtt in","z":"a79b8870bf169f3d","name":"home/temperature5","topic":"home/temperature5","qos":"2","datatype":"auto-detect","broker":"3b7c4b221eb69910","nl":false,"rap":true,"rh":0,"inputs":0,"x":170,"y":220,"wires":[["f647214f1803feb0","234b936925863308","c9b78fe1c3b959f1"]]},{"id":"db6f196b033007be","type":"mqtt in","z":"a79b8870bf169f3d","name":"home/humidity5","topic":"home/humidity5","qos":"2","datatype":"auto-detect","broker":"3b7c4b221eb69910","nl":false,"rap":true,"rh":0,"inputs":0,"x":160,"y":300,"wires":[["eac07e0b2a71ad3f","2834212b1975c877","c9b78fe1c3b959f1"]]},{"id":"f647214f1803feb0","type":"ui_gauge","z":"a79b8870bf169f3d","name":"Device Temperature","group":"e1c18b9de2a81a73","order":1,"width":0,"height":0,"gtype":"gage","title":"Device Temperature","label":"Degree Centigrade","format":"{{value}}","min":0,"max":"80","colors":["#00b500","#e6e600","#ca3838"],"seg1":"28","seg2":"45","className":"","x":530,"y":220,"wires":[]},{"id":"eac07e0b2a71ad3f","type":"ui_gauge","z":"a79b8870bf169f3d","name":"Relative Humidity","group":"e1c18b9de2a81a73","order":2,"width":0,"height":0,"gtype":"gage","title":"Relative Humidity","label":"Percentage","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","className":"","x":540,"y":300,"wires":[]},{"id":"f6c5012ab812ce1c","type":"ui_gauge","z":"a79b8870bf169f3d","name":"Heat index","group":"e1c18b9de2a81a73","order":2,"width":0,"height":0,"gtype":"gage","title":"Heat index","label":"Percentage","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","className":"","x":550,"y":400,"wires":[]},{"id":"cf5ffb0b36989540","type":"mqtt in","z":"a79b8870bf169f3d","name":"home/heatindex5","topic":"home/heatindex5","qos":"2","datatype":"auto-detect","broker":"3b7c4b221eb69910","nl":false,"rap":true,"rh":0,"inputs":0,"x":160,"y":400,"wires":[["f6c5012ab812ce1c","4c23fba062102cbc","c9b78fe1c3b959f1"]]},{"id":"4c23fba062102cbc","type":"debug","z":"a79b8870bf169f3d","name":"debug 11","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":340,"y":520,"wires":[]},{"id":"2834212b1975c877","type":"debug","z":"a79b8870bf169f3d","name":"debug 12","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":340,"y":340,"wires":[]},{"id":"234b936925863308","type":"debug","z":"a79b8870bf169f3d","name":"debug 13","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":340,"y":160,"wires":[]},{"id":"cf7857d130eab6f5","type":"mysql","z":"a79b8870bf169f3d","mydb":"2aec8b90542176b0","name":"","x":1310,"y":660,"wires":[["97bd469ba45d6f24"]]},{"id":"97bd469ba45d6f24","type":"debug","z":"a79b8870bf169f3d","name":"debug 14","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1340,"y":420,"wires":[]},{"id":"e1a0c4f4bc4117c0","type":"debug","z":"a79b8870bf169f3d","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1070,"y":420,"wires":[]},{"id":"c9b78fe1c3b959f1","type":"join","z":"a79b8870bf169f3d","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":770,"y":660,"wires":[["f8a3df8d7bc976db","55674741617539ac"]]},{"id":"f8a3df8d7bc976db","type":"debug","z":"a79b8870bf169f3d","name":"debug 15","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":780,"y":420,"wires":[]},{"id":"55674741617539ac","type":"function","z":"a79b8870bf169f3d","name":"INSERT Data","func":"temp = msg.payload[\"home/temperature5\"];\nhum = msg.payload[\"home/humidity5\"];\nhi = msg.payload[\"home/heatindex5\"];\nmsg.topic = `INSERT INTO SensorData5(temperature, humidity, heatindex) VALUES(${temp},${hum},${hi})`;\nreturn msg;\n","outputs":1,"noerr":6,"initialize":"","finalize":"","libs":[],"x":1030,"y":660,"wires":[["e1a0c4f4bc4117c0","cf7857d130eab6f5"]]},{"id":"553ae0647facba39","type":"ui_table","z":"a79b8870bf169f3d","group":"2fdee068.4551f8","name":"Historical Data","order":3,"width":"0","height":"0","columns":[{"field":"id","title":"S. #","width":"","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"temperature","title":"Temperature","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"humidity","title":"Humidity","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"heatindex","title":"Heat Index","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"reading_time","title":"Time Stamp","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":1080,"y":880,"wires":[]},{"id":"8b09dadb1fcafef6","type":"mysql","z":"a79b8870bf169f3d","mydb":"2aec8b90542176b0","name":"DB","x":870,"y":900,"wires":[["553ae0647facba39","229a474ab818d170"]]},{"id":"d6d3dd9a1d9e3c11","type":"function","z":"a79b8870bf169f3d","name":"","func":"let startDateTime = msg.payload.startDate.slice(0, 10) + \" \" + msg.payload.startTime\nlet endDateTime = msg.payload.endDate.slice(0, 10) + \" \" + msg.payload.endTime\n// SELECT * FROM SensorData5 WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'\nmsg.topic = `SELECT * FROM SensorData5 WHERE 'reading_time' BETWEEN '${startDateTime}' AND '${endDateTime}'`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":620,"y":900,"wires":[["8b09dadb1fcafef6","4e68305564b34da9"]]},{"id":"229a474ab818d170","type":"debug","z":"a79b8870bf169f3d","name":"","active":true,"console":"false","complete":"false","x":1050,"y":800,"wires":[]},{"id":"4e68305564b34da9","type":"debug","z":"a79b8870bf169f3d","name":"","active":true,"console":"false","complete":"false","x":790,"y":800,"wires":[]},{"id":"9dccd93333ed5dd7","type":"ui_form","z":"a79b8870bf169f3d","name":"","label":"","group":"2fdee068.4551f8","order":1,"width":16,"height":5,"options":[{"label":"Start Date","value":"startDate","type":"date","required":true,"rows":null},{"label":"Start Time","value":"startTime","type":"text","required":true,"rows":null},{"label":"End Date","value":"endDate","type":"date","required":true,"rows":null},{"label":"End Time","value":"endTime","type":"text","required":true,"rows":null}],"formValue":{"startDate":"","startTime":"","endDate":"","endTime":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","topicType":"str","splitLayout":false,"className":"","x":370,"y":800,"wires":[["d6d3dd9a1d9e3c11"]]},{"id":"3b7c4b221eb69910","type":"mqtt-broker","name":"","broker":"192.168.100.111","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"e1c18b9de2a81a73","type":"ui_group","name":"Equipment Monitoring","tab":"2669d5cf7077f35a","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"2aec8b90542176b0","type":"MySQLdatabase","name":"sensor_data","host":"127.0.0.1","port":"3306","db":"sesnordata","tz":"","charset":"UTF8"},{"id":"2fdee068.4551f8","type":"ui_group","name":"Select Date time range","tab":"b724c3ac.56f638","order":1,"disp":true,"width":"16","collapse":false},{"id":"2669d5cf7077f35a","type":"ui_tab","name":"Water Meter - Demo Device","icon":"dashboard","order":6,"disabled":false,"hidden":false},{"id":"b724c3ac.56f638","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
2

Welcome to the forum @msashraf

You should use a mysql client to confirm whether the data exists correctly in the database.

3

Yes, as Colin said, always use an external program to verify databases until you know your Node-Red setup is working appropriately.

Can you post the flow you're using to get the data into and out of the database? And do you have any debug or console outputs that show up when you try to put the data in or pull the data out? That might help to figure out where the disconnect exists.

4

Just verified the mysql data in phpmyadmin and data is pooling into respective table correctly.

5

Here is the function which is pooling the data into mysql;

temp = msg.payload["home/temperature5"];

hum = msg.payload["home/humidity5"];

hi = msg.payload["home/heatindex5"];

msg.topic = INSERT INTO SensorData5(temperature, humidity, heatindex) VALUES(${temp},${hum},${hi});

return msg;

6

As i am new user, this forum cant let me upload file. i want to share the flow. Anyways sharing code here;

7 
[
    {
        "id": "a0744beff99507c6",
        "type": "tab",
        "label": "Flow 15",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "4963b4cfc0b129d0",
        "type": "mqtt in",
        "z": "a0744beff99507c6",
        "name": "home/temperature5",
        "topic": "home/temperature5",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "3b7c4b221eb69910",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 170,
        "y": 220,
        "wires": [
            [
                "819967a3da513137",
                "b8788190e4e5738d",
                "dbab5f5d3a723a58"
            ]
        ]
    },
    {
        "id": "19428ae43b42524f",
        "type": "mqtt in",
        "z": "a0744beff99507c6",
        "name": "home/humidity5",
        "topic": "home/humidity5",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "3b7c4b221eb69910",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 160,
        "y": 300,
        "wires": [
            [
                "e0d13496be913029",
                "92c716b74f1d9801",
                "dbab5f5d3a723a58"
            ]
        ]
    },
    {
        "id": "819967a3da513137",
        "type": "ui_gauge",
        "z": "a0744beff99507c6",
        "name": "Device Temperature",
        "group": "e1c18b9de2a81a73",
        "order": 1,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Device Temperature",
        "label": "Degree Centigrade",
        "format": "{{value}}",
        "min": 0,
        "max": "80",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "28",
        "seg2": "45",
        "className": "",
        "x": 530,
        "y": 220,
        "wires": []
    },
    {
        "id": "e0d13496be913029",
        "type": "ui_gauge",
        "z": "a0744beff99507c6",
        "name": "Relative Humidity",
        "group": "e1c18b9de2a81a73",
        "order": 2,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Relative Humidity",
        "label": "Percentage",
        "format": "{{value}}",
        "min": 0,
        "max": "100",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "className": "",
        "x": 540,
        "y": 300,
        "wires": []
    },
    {
        "id": "bd4118b8b154e257",
        "type": "ui_gauge",
        "z": "a0744beff99507c6",
        "name": "Heat index",
        "group": "e1c18b9de2a81a73",
        "order": 2,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "Heat index",
        "label": "Percentage",
        "format": "{{value}}",
        "min": 0,
        "max": "100",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "className": "",
        "x": 550,
        "y": 400,
        "wires": []
    },
    {
        "id": "d1335d399c8c53ef",
        "type": "mqtt in",
        "z": "a0744beff99507c6",
        "name": "home/heatindex5",
        "topic": "home/heatindex5",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "3b7c4b221eb69910",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 160,
        "y": 400,
        "wires": [
            [
                "bd4118b8b154e257",
                "4df81fbda1ef721a",
                "dbab5f5d3a723a58"
            ]
        ]
    },
    {
        "id": "4df81fbda1ef721a",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "debug 11",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 340,
        "y": 520,
        "wires": []
    },
    {
        "id": "92c716b74f1d9801",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "debug 12",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 340,
        "y": 340,
        "wires": []
    },
    {
        "id": "b8788190e4e5738d",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "debug 13",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 340,
        "y": 160,
        "wires": []
    },
    {
        "id": "b15075d7a6b72c53",
        "type": "mysql",
        "z": "a0744beff99507c6",
        "mydb": "2aec8b90542176b0",
        "name": "",
        "x": 1310,
        "y": 660,
        "wires": [
            [
                "607336eb0f4e5090"
            ]
        ]
    },
    {
        "id": "607336eb0f4e5090",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "debug 14",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1340,
        "y": 420,
        "wires": []
    },
    {
        "id": "979b1942fe42c835",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 1070,
        "y": 420,
        "wires": []
    },
    {
        "id": "dbab5f5d3a723a58",
        "type": "join",
        "z": "a0744beff99507c6",
        "name": "",
        "mode": "custom",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "",
        "count": "3",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 770,
        "y": 660,
        "wires": [
            [
                "a495224721d76436",
                "958e3655f13fb829"
            ]
        ]
    },
    {
        "id": "a495224721d76436",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "debug 15",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 780,
        "y": 420,
        "wires": []
    },
    {
        "id": "958e3655f13fb829",
        "type": "function",
        "z": "a0744beff99507c6",
        "name": "INSERT Data",
        "func": "temp = msg.payload[\"home/temperature5\"];\nhum = msg.payload[\"home/humidity5\"];\nhi = msg.payload[\"home/heatindex5\"];\nmsg.topic = `INSERT INTO SensorData5(temperature, humidity, heatindex) VALUES(${temp},${hum},${hi})`;\nreturn msg;\n",
        "outputs": 1,
        "noerr": 6,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1030,
        "y": 660,
        "wires": [
            [
                "979b1942fe42c835",
                "b15075d7a6b72c53"
            ]
        ]
    },
    {
        "id": "0b79199f40a3dd0e",
        "type": "ui_table",
        "z": "a0744beff99507c6",
        "group": "2fdee068.4551f8",
        "name": "Historical Data",
        "order": 3,
        "width": "0",
        "height": "0",
        "columns": [
            {
                "field": "id",
                "title": "S. #",
                "width": "",
                "align": "center",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            },
            {
                "field": "temperature",
                "title": "Temperature",
                "width": "",
                "align": "left",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            },
            {
                "field": "humidity",
                "title": "Humidity",
                "width": "",
                "align": "left",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            },
            {
                "field": "heatindex",
                "title": "Heat Index",
                "width": "",
                "align": "left",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            },
            {
                "field": "reading_time",
                "title": "Time Stamp",
                "width": "",
                "align": "left",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            }
        ],
        "outputs": 0,
        "cts": false,
        "x": 1080,
        "y": 880,
        "wires": []
    },
    {
        "id": "b998520e111ca3f0",
        "type": "mysql",
        "z": "a0744beff99507c6",
        "mydb": "2aec8b90542176b0",
        "name": "DB",
        "x": 870,
        "y": 900,
        "wires": [
            [
                "0b79199f40a3dd0e",
                "8bba308d54e68579"
            ]
        ]
    },
    {
        "id": "46235317e129e339",
        "type": "function",
        "z": "a0744beff99507c6",
        "name": "",
        "func": "let startDateTime = msg.payload.startDate.slice(0, 10) + \" \" + msg.payload.startTime\nlet endDateTime = msg.payload.endDate.slice(0, 10) + \" \" + msg.payload.endTime\n// SELECT * FROM SensorData5 WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'\nmsg.topic = `SELECT * FROM SensorData5 WHERE 'reading_time' BETWEEN '${startDateTime}' AND '${endDateTime}'`\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 620,
        "y": 900,
        "wires": [
            [
                "b998520e111ca3f0",
                "f5d548d3180e417d"
            ]
        ]
    },
    {
        "id": "8bba308d54e68579",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "",
        "active": true,
        "console": "false",
        "complete": "false",
        "x": 1050,
        "y": 800,
        "wires": []
    },
    {
        "id": "f5d548d3180e417d",
        "type": "debug",
        "z": "a0744beff99507c6",
        "name": "",
        "active": true,
        "console": "false",
        "complete": "false",
        "x": 790,
        "y": 800,
        "wires": []
    },
    {
        "id": "403aabea300ec6b1",
        "type": "ui_form",
        "z": "a0744beff99507c6",
        "name": "",
        "label": "",
        "group": "2fdee068.4551f8",
        "order": 1,
        "width": 16,
        "height": 5,
        "options": [
            {
                "label": "Start Date",
                "value": "startDate",
                "type": "date",
                "required": true,
                "rows": null
            },
            {
                "label": "Start Time",
                "value": "startTime",
                "type": "text",
                "required": true,
                "rows": null
            },
            {
                "label": "End Date",
                "value": "endDate",
                "type": "date",
                "required": true,
                "rows": null
            },
            {
                "label": "End Time",
                "value": "endTime",
                "type": "text",
                "required": true,
                "rows": null
            }
        ],
        "formValue": {
            "startDate": "",
            "startTime": "",
            "endDate": "",
            "endTime": ""
        },
        "payload": "",
        "submit": "submit",
        "cancel": "cancel",
        "topic": "",
        "topicType": "str",
        "splitLayout": false,
        "className": "",
        "x": 370,
        "y": 800,
        "wires": [
            [
                "46235317e129e339"
            ]
        ]
    },
    {
        "id": "3b7c4b221eb69910",
        "type": "mqtt-broker",
        "name": "",
        "broker": "192.168.100.111",
        "port": "1883",
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": "4",
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    },
    {
        "id": "e1c18b9de2a81a73",
        "type": "ui_group",
        "name": "Equipment Monitoring",
        "tab": "2669d5cf7077f35a",
        "order": 1,
        "disp": true,
        "width": "6",
        "collapse": false,
        "className": ""
    },
    {
        "id": "2aec8b90542176b0",
        "type": "MySQLdatabase",
        "name": "sensor_data",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "sesnordata",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "2fdee068.4551f8",
        "type": "ui_group",
        "name": "Select Date time range",
        "tab": "b724c3ac.56f638",
        "order": 1,
        "disp": true,
        "width": "16",
        "collapse": false
    },
    {
        "id": "2669d5cf7077f35a",
        "type": "ui_tab",
        "name": "Water Meter - Demo Device",
        "icon": "dashboard",
        "order": 6,
        "disabled": false,
        "hidden": false
    },
    {
        "id": "b724c3ac.56f638",
        "type": "ui_tab",
        "name": "Home",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]

[post editied by moderator to make it able to import]

8

You don't need to upload a file. Look up at the top of your post window and you'll see an icon that looks like this:

</>

Click on it and you'll get a section in your code that looks like this:
image
Simply export your flow in Node-Red and paste the export here. If you click on the JSON tab in the export window and use the compact switch at the bottom, it will spit it out in one nice line. We can copy that flow export into our own Node-Red flows and see exactly what you're seeing.

9

Since you have determined that the data in the db are correct, all you need to do is to show us how you are querying the data and what is in a debug node attached to the db node. You don't need to show the complete flow.

I presume that you have looked at what you are getting out of the db node?

10

Flow code shared.

11

Right. I missed that, sorry.

Ok. Looking into it, this is what I'm noticing. First off, you're getting data into it, like you say, so it's not a database configuration issue. The same configuration is used for both pushing and pulling, so one working means the other should work. Second, you're getting data into you're gauges, which means the data is valid, so no issues there. So we know through external checking of the database that the whole upper part of your flow is working normally.

That leaves the lower part of the flow. We can cross of the database node since again, it uses the same configuration as the upper. We can also cross off the table since you have a debug node getting the same data and you say it's not coming through. So it has to be something before that. That leaves only your form and function nodes.

And that's where I see a discrepancy. In your form node, you're asking for a start date and time as well as an end date and time. No issues there. Hard to have something wrong with the form node. And your function node is combining them into a single variable to use for the timestamps to retrieve from the database. Again, all well and good. And then you query for anything between those to time stamps.

But I don't see anything in your INSERT node to store a time stamp for you to compare against.

I'm not saying it isn't there. I'm just saying that I don't see it implemented. It could be in a background process running on your database server to automatically add one when data comes in. I don't know. You know what's in your database and how it gets there.

So could it be missing timestamps? Sure. Could it also be the semicolons missing in your SELECT function messing everything up? Possibly. I did notice that you used them on your INSERT function, but not your SELECT function, which may be what's holding it up.

All else fails, run a SELECT * FROM SensorData5 from your function and see what you get back.

1 Like
12

The query in the function to get the data from Mysql is not working properly.

13

Show us the query you are sending in and the data you are getting out, and tell us what is not correct.

14 
let startDateTime = msg.payload.startDate.slice(0, 10) + " " + msg.payload.startTime
let endDateTime = msg.payload.endDate.slice(0, 10) + " " + msg.payload.endTime
// SELECT * FROM SensorData5 WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'
msg.topic = `SELECT * FROM SensorData5 WHERE reading_time BETWEEN '${startDateTime}' AND '${endDateTime}'`

return msg;
15

I think you are right because the timestamp field is being inserted at db level. That is not part of insert query. Please let me know what to add in insert query to carry the event timestamp from source rather the time of event being inserted in db. This might work.

Secondly the date format in form is "mm-dd-yyyy" and the format in database field is "yyyy-mm-dd". Will this be a problem? Please see the query for searching record too.

16

This is debug output of search function (db query);

02/11/2022, 03:01:09node: e8302b187d024940SELECT * FROM SensorData5 WHERE reading_time BETWEEN '2022-11-01 02:50:01' AND '2022-11-01 02:55:02' : msg.payload : Object

object

startDate: "2022-11-01T19:00:00.000Z"

startTime: "02:50:01"

endDate: "2022-11-01T19:00:00.000Z"

endTime: "02:55:02"

17

Yes, that could be your problem. The database driver may be seeing a four digit month and panicking. A simple remedy is to chunk up your date further so you can arrange things as needed. Instead of carving off all 10 characters for the date, carve off two for the month, carve the two for the date and then finish off with the year. Rearrange the fields so you get a string with the format being stored in the database.

But it all depends on how the data is stored in the database. Some database drivers let you send in a string that is stored and interpreted as a string. Some convert it to an absolute value from the epoch time defined in the database standard. Some require you to send the epoch time itself and store it that way.

In any case, do what you can to match the query to the database. And as far as adding the timestamp to the insert query, look at this page to get an idea of how to pull the local time in Javascript. Convert that time into whatever is meaningful for your database and then simply add it to your INSERT. The conversion is what you're going to need to know how to do based on how your database handles it.

Hope that helps.

1 Like
18

Fortunately I am now getting the data visualized in dashboard table. But now the time it is showing is in UTC, where as we need this time to be shown in local time (GMT+5).

The timestamp still is inserted at db level. But i am working on inserting it from function at source.

19

Only did one thing that is changed the input type in Form from "date" to "text". Now data is populated in Table.

20

Perfect! Sometimes it's the simple things that break everything loose.

My guess is that you're running on a Linux box. They default their system clocks to UTC and then compensate in software for the offset. If Javascript is pulling the clock from the system directly and not the OS, it might account for the discrepancy. A simple answer would just be to add 18,000,000 (equivalent of five hours in milliseconds) to your date-time value in your code before you write it to your table. That should correct the time difference for now.