I am storing certain data in MySql from Node Red. Now as I want to visualize data in chart in dashboard it is showing wrong date and time in graph. Whereas the X & Y titles are shown correctly.
Here is flow for furthe review;
[{"id":"1d3884ecc879e720","type":"tab","label":"Historical Charts","disabled":false,"info":"","env":[]},{"id":"50f43e95495a8504","type":"mysql","z":"1d3884ecc879e720","mydb":"2aec8b90542176b0","name":"DB","x":570,"y":540,"wires":[["13662fc9d137bab0","ff0fed534a9917d1"]]},{"id":"77b86b3e369c9f13","type":"function","z":"1d3884ecc879e720","name":"","func":"let device_id = msg.payload.device_id;\nlet startDateTime = msg.payload.startDate.slice(0, 10)+\" \"+msg.payload.startTime;\nlet endDateTime = msg.payload.endDate.slice(0, 10)+\" \"+msg.payload.endTime;\nmsg.topic = `SELECT volume,reading_time FROM water_flow_data WHERE device_id = '${device_id}' HAVING reading_time BETWEEN '${startDateTime}' AND '${endDateTime}'`;\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":540,"wires":[["9e7accc54934309f","50f43e95495a8504"]]},{"id":"13662fc9d137bab0","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":770,"y":440,"wires":[]},{"id":"9e7accc54934309f","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":530,"y":440,"wires":[]},{"id":"acf1779e57a653c0","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":290,"y":440,"wires":[]},{"id":"ff0fed534a9917d1","type":"function","z":"1d3884ecc879e720","name":"Data for Chart","func":"let volume = [];\nlet reading_time = [];\nmsg.payload.forEach(function (value) {\n volume.push(value['volume']);\n reading_time.push(value['reading_time']);\n});\n\nmsg.payload = [{\n series: \"\",\n data: [volume],\n labels: reading_time\n}];\nreturn msg;\n\n\n\n\n\n\n\n\n\n//let volume = [];\n//let day = [];\n//msg.payload.forEach(function(value) {\n// volume.push(value['volume']);\n// day.push(value['day']);\n//});\n\n//msg.payload = [{\n// series: \"\",\n// data: [volume],\n// labels: day\n//}];\n//return msg;\n\n\n\n\n\n//var data = [];\n//var month = [];\n//msg.payload.forEach(function (value) {\n// data.push(value['avg(temp)']);\n// month.push(value['the_month']);\n//});\n\n//msg.payload = [{\n// series: \"\",\n// data: [data],\n// labels: month\n//}];\n//return msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":840,"y":540,"wires":[["66b43fa2b567e061","7a6a91deca9c2928"]]},{"id":"c5cbc633c5cbcef6","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":1410,"y":440,"wires":[]},{"id":"66b43fa2b567e061","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":1070,"y":440,"wires":[]},{"id":"eabc385cc081a58f","type":"ui_form","z":"1d3884ecc879e720","name":"","label":"","group":"694c7ecf92d6d173","order":2,"width":24,"height":1,"options":[{"label":"Device ID","value":"device_id","type":"text","required":true,"rows":null},{"label":"Start Date","value":"startDate","type":"text","required":false,"rows":null},{"label":"Start Time","value":"startTime","type":"text","required":false,"rows":null},{"label":"End Date","value":"endDate","type":"text","required":false,"rows":null},{"label":"End Time","value":"endTime","type":"text","required":false,"rows":null}],"formValue":{"device_id":"","startDate":"","startTime":"","endDate":"","endTime":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","topicType":"str","splitLayout":false,"className":"","x":90,"y":540,"wires":[["acf1779e57a653c0","77b86b3e369c9f13"]]},{"id":"7a6a91deca9c2928","type":"ui_chart","z":"1d3884ecc879e720","name":"","group":"694c7ecf92d6d173","order":1,"width":0,"height":0,"label":"Water Supply - Recorded History","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"30","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1130,"y":540,"wires":[["c5cbc633c5cbcef6"]]},{"id":"2aec8b90542176b0","type":"MySQLdatabase","name":"sensor_data","host":"127.0.0.1","port":"3306","db":"sesnordata","tz":"+05:00 UTC","charset":"UTF8"},{"id":"694c7ecf92d6d173","type":"ui_group","name":"","tab":"24a9f3992041ce83","order":1,"disp":true,"width":24,"collapse":false,"className":""},{"id":"24a9f3992041ce83","type":"ui_tab","name":"Historical Charts","icon":"dashboard","order":12,"disabled":false,"hidden":false}]
Show us an example of the data you are sending and tell us what date/time it is showing for that data and what you expect. Are you sending the timestamps in milliseconds?
I meant to show us what it looks like in a debug node showing what is going to the chart.
Also show us how you have configured the chart
Answer # 1: The issue is with Label which is shown in debug as "2022-11-09T01:19:04.000Z". Also see the below mentioned snaps shots for both "data" and "label".
Answer # 2: Please find below the snap shots.
You are not formatting the data correctly for a time based line chart. This page describes how it should be. In particulare see the first example in the section Line Charts.
Still wont be able to solve the issue. Storing Data and Time as string in DB and still chart label on points is not correct time rather it is showing UTC time which is 5 hours old as compared to local time. Find no configuration in chart settings relevant to this.
Show us what you see going into the chart now.
Also, what timezone are the node red server and the machine running the browser set to?
Here is code for function that pushes data into chart. And the snaps shots for both chart and function debug node is attached below.
The time zone configured on node red server is GMT +5 and same on client machine running the browser.
let volume = [];
let date1 = [];
msg.payload.forEach(function (value) {
volume.push(value['volume']);
date1.push(value['date1']);
});
msg.payload = [{
series: "V",
data: [volume],
labels: date1
}];
return msg;
Also the code for flow is here;
[{"id":"1d3884ecc879e720","type":"tab","label":"Historical Charts","disabled":false,"info":"","env":[]},{"id":"50f43e95495a8504","type":"mysql","z":"1d3884ecc879e720","mydb":"2aec8b90542176b0","name":"DB","x":570,"y":540,"wires":[["13662fc9d137bab0","ff0fed534a9917d1"]]},{"id":"77b86b3e369c9f13","type":"function","z":"1d3884ecc879e720","name":"","func":"let device_id = msg.payload.device_id;\nlet startDate = msg.payload.startDate.slice(0, 10)+\"\";\nlet endDate = msg.payload.endDate.slice(0, 10)+\"\";\nmsg.topic = `SELECT volume,date1 FROM water_flow_data WHERE device_id = '${device_id}' HAVING date1 BETWEEN '${startDate}' AND '${endDate}'`;\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":540,"wires":[["9e7accc54934309f","50f43e95495a8504"]]},{"id":"13662fc9d137bab0","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":770,"y":440,"wires":[]},{"id":"9e7accc54934309f","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":530,"y":440,"wires":[]},{"id":"acf1779e57a653c0","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":290,"y":440,"wires":[]},{"id":"ff0fed534a9917d1","type":"function","z":"1d3884ecc879e720","name":"Data for Chart","func":"let volume = [];\nlet date1 = [];\nmsg.payload.forEach(function (value) {\n volume.push(value['volume']);\n date1.push(value['date1']);\n});\n\nmsg.payload = [{\n series: \"V\",\n data: [volume],\n labels: date1\n}];\nreturn msg;\n\n\n\n//let volume = [];\n//let day = [];\n//msg.payload.forEach(function(value) {\n// volume.push(value['volume']);\n// day.push(value['day']);\n//});\n\n//msg.payload = [{\n// series: \"\",\n// data: [volume],\n// labels: day\n//}];\n//return msg;\n\n\n\n\n\n//var data = [];\n//var month = [];\n//msg.payload.forEach(function (value) {\n// data.push(value['avg(temp)']);\n// month.push(value['the_month']);\n//});\n\n//msg.payload = [{\n// series: \"\",\n// data: [data],\n// labels: month\n//}];\n//return msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":840,"y":540,"wires":[["66b43fa2b567e061","7a6a91deca9c2928"]]},{"id":"c5cbc633c5cbcef6","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":1410,"y":440,"wires":[]},{"id":"66b43fa2b567e061","type":"debug","z":"1d3884ecc879e720","name":"","active":true,"console":"false","complete":"false","x":1070,"y":440,"wires":[]},{"id":"eabc385cc081a58f","type":"ui_form","z":"1d3884ecc879e720","name":"","label":"","group":"694c7ecf92d6d173","order":2,"width":24,"height":1,"options":[{"label":"Device ID","value":"device_id","type":"text","required":true,"rows":null},{"label":"Start Date","value":"startDate","type":"text","required":false,"rows":null},{"label":"End Date","value":"endDate","type":"text","required":false,"rows":null}],"formValue":{"device_id":"","startDate":"","endDate":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","topicType":"str","splitLayout":false,"className":"","x":90,"y":540,"wires":[["acf1779e57a653c0","77b86b3e369c9f13"]]},{"id":"7a6a91deca9c2928","type":"ui_chart","z":"1d3884ecc879e720","name":"","group":"694c7ecf92d6d173","order":1,"width":0,"height":0,"label":"Water Supply - Recorded History","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"30","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1130,"y":540,"wires":[["c5cbc633c5cbcef6"]]},{"id":"2aec8b90542176b0","type":"MySQLdatabase","name":"sensor_data","host":"127.0.0.1","port":"3306","db":"sesnordata","tz":"+05:00 UTC","charset":"UTF8"},{"id":"694c7ecf92d6d173","type":"ui_group","name":"","tab":"24a9f3992041ce83","order":1,"disp":true,"width":24,"collapse":false,"className":""},{"id":"24a9f3992041ce83","type":"ui_tab","name":"Historical Charts","icon":"dashboard","order":12,"disabled":false,"hidden":false}]
You still are not formatting the data correctly for a time based line chart. Did you look at the first example in the section Line Charts on the page I linked to?
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
