Hi i using Sqlite database and store my temperature readings, i want to get it back in my chart when i have for example reboot my pi.
So i am receiving it back but the chart says bad data.
[
{
"id": "a5f54cfd7a4398c2",
"type": "join",
"z": "f5573b729c358c30",
"name": "",
"mode": "custom",
"build": "array",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"timeout": "",
"count": "",
"x": 1230,
"y": 980,
"wires": [
[
"65c4ac924b837210"
]
]
},
{
"id": "9e9cf1fcd9d295c7",
"type": "sqlite",
"z": "f5573b729c358c30",
"mydb": "d61203bc8b18fecb",
"sqlquery": "msg.topic",
"sql": "",
"name": "temp",
"x": 1090,
"y": 980,
"wires": [
[
"a5f54cfd7a4398c2"
]
]
},
{
"id": "10be4ea27a8be610",
"type": "function",
"z": "f5573b729c358c30",
"name": "SQL",
"func": "// This will handle any device and any attribute as long as it is in the DB\nvar Week = 604800000 ; //7 Days\nvar Day = 86400000 ; // 1 Days\nvar d = new Date();\nvar epoch = d.getTime();\nvar fromdate = epoch - Day;\nvar enddate = epoch;\nvar output = [];\n\nfor (var property in msg.payload) {\n if (msg.payload.hasOwnProperty(property)) {\n //output.push({ payload: property + \" has value \"+msg.payload[property] });\n if (msg.payload[property]) {\n var parts = property.split(\"/\");\n if (parts[0]===\"period\") {\n switch (parts[1]) {\n case \"today\":\n fromdate = epoch-Day;\n enddate = epoch;\n break;\n case \"yesterday\":\n fromdate = epoch-2*Day;\n enddate = epoch-Day;\n break;\n case \"week\":\n fromdate = epoch-Week;\n enddate = epoch;\n break;\n }\n } else {\n output.push({ topic: \"SELECT * FROM temp WHERE device='tado' AND sensor='övervåning' AND epoch >= \" + fromdate + \" AND epoch <= \" + enddate });\n }\n }\n }\n}\n\noutput[output.length-1].complete=true;\n\nreturn [ output ];\n\n//msg.topic = \"SELECT * FROM sensor_data WHERE device='growatt' AND sensor='power' AND epoch >= \" + fromdate + \" AND epoch <= \" + epoch ;\n//return msg;",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 910,
"y": 980,
"wires": [
[
"9e9cf1fcd9d295c7"
]
]
},
{
"id": "52faf50518cdfbc4",
"type": "ui_chart",
"z": "f5573b729c358c30",
"name": "",
"group": "3492783650a37422",
"order": 1,
"width": 0,
"height": 0,
"label": "charttest",
"chartType": "line",
"legend": "false",
"xformat": "%a %H:%M",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": 1,
"removeOlderPoints": "",
"removeOlderUnit": "604800",
"cutout": 0,
"useOneColor": false,
"useUTC": false,
"colors": [
"#1f77b4",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 1520,
"y": 980,
"wires": [
[]
]
},
{
"id": "195b40d060715841",
"type": "inject",
"z": "f5573b729c358c30",
"name": "inject JSON object",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": "",
"topic": "",
"payload": "{ \"period/today\": true, \"period/yesterday\": false, \"period/week\": false, \"device\": false, \"complete\":true }",
"payloadType": "json",
"x": 710,
"y": 980,
"wires": [
[
"10be4ea27a8be610"
]
]
},
{
"id": "65c4ac924b837210",
"type": "function",
"z": "f5573b729c358c30",
"name": "Prep",
"func": "var msg2 = [];\n\n\n// this is the logic when there are multiple data sets are received\nfor (var i=0; i<msg.payload.length; i++) {\n var output = [];\n ({\n \"seriers \": ( \"x\" )\n })\n for (var j=0; j<msg.payload[i].length; j++) {\n output.push([msg.payload[i][j].epoch, msg.payload[i][j].value]);\n }\n msg2.push({ key: +msg.payload[i][0].device+\"/\"+msg.payload[i][0].data_type, values : output});\n //msg2.push({ key: \"test\", values : output});\n\n\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;\n\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1370,
"y": 980,
"wires": [
[
"52faf50518cdfbc4",
"e2a88d51ea0860d7"
]
]
},
{
"id": "e2a88d51ea0860d7",
"type": "debug",
"z": "f5573b729c358c30",
"name": "debug 78",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 1420,
"y": 1080,
"wires": []
},
{
"id": "d61203bc8b18fecb",
"type": "sqlitedb",
"db": "/home/ander/sensordata.db",
"mode": "RWC"
},
{
"id": "3492783650a37422",
"type": "ui_group",
"name": "Group 1",
"tab": "9307492ee488c070",
"order": 1,
"disp": true,
"width": 6
},
{
"id": "9307492ee488c070",
"type": "ui_tab",
"name": "Test återtäll databas",
"icon": "dashboard",
"order": 9,
"disabled": false,
"hidden": false
}
]
