Node-Red / sqlite / Charts

[Harakaio]

Hi there,

I'm struggling with JavaScript.
Via Form-Node I'm selecting the datapoints which I want to extract from sqlite and show them in a chart.
From sqlite I receive this: Pic1&2.
Pic1:

Pic2:

After that I let run a prep' node, which transforms my msg.payload into another array (Pic3-5.jpg).
Pic3:

Pic4:

Pic5:

But I need to transform that into an array which is able to be shown at the chart-node from dashboard - structure see here (Picture6.jpg).

Pic6:

If there is a need for the .db-file, please tell me how to upload it here. It is about 3.7 MB big but not allowed (file extension .db)

But: I'm too dumb and not really familiar with javascript to rearrange the content of this array.

This is the flow:

[{"id":"dd06a909.e84d28","type":"function","z":"7783ee4a.75705","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 timestamp = d.getTime();\nvar fromdate = timestamp - Day;\nvar enddate = timestamp;\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 = timestamp-Day;\n                        enddate = timestamp;\n                        break;\n                    case \"yesterday\":\n                        fromdate = timestamp-2*Day;\n                        enddate = timestamp-Day;\n                        break;\n                    case \"week\":\n                        fromdate = timestamp-Week;\n                        enddate = timestamp;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM DATA WHERE device='\"+parts[0]+\"' AND sensorname='\"+parts[1]+\"' AND timestamp >= \" + fromdate + \" AND timestamp <= \" + 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,"noerr":0,"initialize":"","finalize":"","x":310,"y":1020,"wires":[["c411fc78.dabd9","91e8f4b7.b873f8"]]},{"id":"9f2c80f0.25273","type":"ui_form","z":"7783ee4a.75705","name":"Data points","label":"","group":"51e1679f.558fe8","order":2,"width":0,"height":0,"options":[{"label":"Zeitraum: heute","value":"period/today","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: gestern","value":"period/yesterday","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: diese Woche","value":"period/week","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftfeuchte","value":"device1/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Temperatur","value":"device1/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftdruck","value":"device1/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftfeuchte","value":"device2/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Temperatur","value":"device2/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftdruck","value":"device2/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftfeuchte","value":"device3/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Temperatur","value":"device3/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftdruck","value":"device3/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftfeuchte","value":"device4/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Temperatur","value":"device4/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftdruck","value":"device4/pressure","type":"checkbox","required":false,"rows":null}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"device1/humidity":false,"device1/temperature":false,"device1/pressure":false,"device2/humidity":false,"device2/temperature":false,"device2/pressure":false,"device3/humidity":false,"device3/temperature":false,"device3/pressure":false,"device4/humidity":false,"device4/temperature":false,"device4/pressure":false},"payload":"","submit":"send","cancel":"stop","topic":"","topicType":"str","splitLayout":false,"x":130,"y":1020,"wires":[["8e09d5fa.cfe388","dd06a909.e84d28"]]},{"id":"8e09d5fa.cfe388","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":310,"y":940,"wires":[]},{"id":"c411fc78.dabd9","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":940,"wires":[]},{"id":"91e8f4b7.b873f8","type":"change","z":"7783ee4a.75705","name":"device","rules":[{"t":"change","p":"topic","pt":"msg","from":"device1","fromt":"str","to":"8b3790","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device2","fromt":"str","to":"7d945d","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device3","fromt":"str","to":"8b39b7","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device4","fromt":"str","to":"7d9ba3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":1020,"wires":[["3f81ad79.b5ac92","a2f94e78.758ad"]]},{"id":"3f81ad79.b5ac92","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":630,"y":940,"wires":[]},{"id":"a2f94e78.758ad","type":"sqlite","z":"7783ee4a.75705","mydb":"8274941a.73344","sqlquery":"msg.topic","sql":"","name":"","x":710,"y":1020,"wires":[["23de8469.09a0dc","55cd3e0.2e3e6c4"]]},{"id":"28c78e23.211732","type":"function","z":"7783ee4a.75705","name":"Prep","func":"var msg2 = [];\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    for (var j=0; j<msg.payload[i].length; j++) {\n        output.push([msg.payload[i][j].timestamp, msg.payload[i][j].value]);\n    }\n    msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensorname, values : output});\n    //msg2.push({ key: \"test\", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1070,"y":1020,"wires":[["a74de472.7babf8"]]},{"id":"a74de472.7babf8","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1210,"y":940,"wires":[]},{"id":"c37e251d.04ec58","type":"ui_chart","z":"7783ee4a.75705","name":"Chart","group":"ffc23bf3.cc9cf8","order":1,"width":0,"height":0,"label":"","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"604800","cutout":"","useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1350,"y":1020,"wires":[[]]},{"id":"23de8469.09a0dc","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":890,"y":940,"wires":[]},{"id":"55cd3e0.2e3e6c4","type":"join","z":"7783ee4a.75705","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":930,"y":1020,"wires":[["94ec6043.75c1b","28c78e23.211732"]]},{"id":"94ec6043.75c1b","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1050,"y":1100,"wires":[]},{"id":"4f2675e3.cb8bcc","type":"function","z":"7783ee4a.75705","name":"Prep2","func":"\nvar msg2 = [];\nvar output = [];\nfor (var i=0; i<msg.payload.length;i++) {\n    for (var j=0; j<msg.payload[i].length; j++) {\n        output.push([msg.payload[i][j].timestamp, msg.payload[i][j].value]);\n    }\n    msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensorname, values : output});\n}\n\nmsg.payload=msg;\nreturn msg;\n\n\n\n\n/*** \nvar msg2 = [];\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    for (var j=0; j<msg.payload[i].length; j++) {\n        output.push([msg.payload[i][j].timestamp, msg.payload[i][j].value]);\n    }\n    msg2.push({ key: msg.payload[i][0].device+\"/\"+msg.payload[i][0].sensorname, values : output});\n    //msg2.push({ key: \"test\", values : output});\n}\n\nmsg.payload=msg2;\n//msg.payload = [ { key: \"Power\", values : output} ];\n//msg.topic = \"Power\";\nreturn msg;\n***/","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1210,"y":1020,"wires":[["da3d5b41.18e0d8"]]},{"id":"da3d5b41.18e0d8","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1350,"y":940,"wires":[]},{"id":"51e1679f.558fe8","type":"ui_group","name":"Selection","tab":"8edd66b5.883ed8","order":1,"disp":true,"width":"6","collapse":false},{"id":"8274941a.73344","type":"sqlitedb","db":"/opt/sqlite/wetterdatenbank.db","mode":"RW"},{"id":"ffc23bf3.cc9cf8","type":"ui_group","name":"Report","tab":"8edd66b5.883ed8","order":2,"disp":true,"width":"6","collapse":false},{"id":"8edd66b5.883ed8","type":"ui_tab","name":"sqliteReport","icon":"dashboard","order":9,"disabled":false,"hidden":false}]

Can you help me?

Thanks!

Edit:
Change-Log:
1 - rearrange of the pictures
2 - add change-log to this post

[Steve-Mcl]

@Harakaio

From sqlite I receive this: Pic1&2.
Pic1:

If you change your SQL QUERY to only return the timestamp and value you can feed it into this function & it will do most of the work for you. NOTE: it expectes timestamp not Timestamp - you can either edit the function or change the query (e.g. select Timestamp as timestamp)

[Harakaio]

Unfortunately this does not work. My database schema is "timestamp, device, sensor, value".
Therefore it is so far not possible to get the data in the structure we need for your function node.

/* DB Data (Raw Data)
[
{
"timestamp":1620221823157,
"device":"8b3790",
"sensorname":"humidity",
"value":37.02
},
]
/
/ Step between - already accomplished:
[
{
"key":"8b3790/humidity",
"values":[
[1620221823157,37.02],
[1620221883198,37.02]
}
]
/
/ Desired format
[{
"series": ["temp", "humidity"],
"data": [
[{ "x": 1504029632890, "y": 5 }, //series 1 data
{ "x": 1504029636001, "y": 4 },
],
[{ "x": 1504029633514, "y": 6 }, //series 2 data
{ "x": 1504029636622, "y": 7 },
],
],
"labels": [""]
}]
*/

For what I'm understanding:
the msg.payload.values in the step between can be merged to msg.payload.data
But I'm struggling to create an array from one single "key" value....

[Steve-Mcl]

@Harakaio

Unfortunately this does not work. My database schema is "timestamp, device, sensor, value"

Yes, i see you SELECT * however I suggested you change the SQL query to only return timestamp and value. Did you try that?

[Harakaio]

Yepp, that works.
I'll receive some data in the dashboard-node:

unfortunately, my database schema is set up in such a way that i have stored the sensor i am reading in an extra cell and not as an extra row like you did in your example.
here is my database schema:
CREATE TABLE DATA ( timestamp integer not null, device text not null, sensorname text not null, value float, Primary key (timestamp,device,sensorname) );

[Harakaio]

My database export looks like this now:

and the graph for this looks like this:

[Steve-Mcl]

You are still selecting all columns. Why? Just select the timestamp and value where sensor = whatever.

If necessary, set the value column name to the name of the sensor e.g... select Timestamp as timestamp, value as '7d945d temperature' from table where sensor = 'temperature' and device = '7d945d';

[Harakaio]

Thanks. That is working!
I'm appreciating your help really!

How do you select multiple devices to be shown in one chart?

[Steve-Mcl]

Arrange your query to return data like ..

timestamp, sensor1, sensor2.

The dynamic function will figure it out.

[Colin]

@Steve-Mcl

Arrange your query to return data like ..

timestamp, sensor1, sensor2.

I don't think it is possible for @Harakaio to do that, since each row in the db only has one measurement, which one it is being determined by the device/sensor fields.

[Steve-Mcl]

You can, using 'case when' or nested queries (and fiddling with the timestamps) but yeah it's not ideal.

Not sure if the UI chart is well suited for multiple series with different timestamps. You got any ideas Colin?

[Colin]

@Steve-Mcl

Not sure if the UI chart is well suited for multiple series with different timestamps.

I don't think that is an issue, the data format for pre-filling the chart has separate sets of data for each series so the fact the timestamps are not aligned is not an issue.

One way would be to read the whole set in then iterate the array, building the data structure as I go. Each line would be tested to see which sensor it relates to and then add the value for the array for that sensor.

However there is probably a much easier way. The data can be added to the chart one sample at a time by providing time stamp and topic with each sample. So for each record in the database generate a msg.payload with that sample in, something like
{topic:"temperature", payload:22, timestamp:1520527095000}
where the topic depends on which sensor it is, and pass those direct to the chart, after first sending it an empty array to clear the chart. That use is not mentioned in the help text seen in the editor but is in the more extensive readme at node-red-dashboard/Charts.md at master · node-red/node-red-dashboard · GitHub

[Steve-Mcl]

@Harakaio here is an example putting 2 values on the chart using the schema of your data

NOTE: your query will need to be such that it returns the items of interest in one go e.g.

SELECT * FROM sensor_data WHERE device='8b3790' -- AND epoch >= blah blah

... assuming device 8b3790 has both temperature and humidity

The function that gets arranges your data...

//helper functions
const getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch
const getValue = (e) => e.value //returns the value field
const getSeriesName = (e) => e.device + "/" + e.sensorname //generate a key / series name e.g. "device/sensorname"

//build graph data
let series = [];
let seriesNames = [];
let seriesCount = 0;
const data = msg.payload;

//loop each row and build an array in the required format
for (let index = 0; index < msg.payload.length; index++) {
    const row = msg.payload[index];
    const seriesName = getSeriesName(row);
    if (seriesNames.includes(seriesName) == false) {
        series[seriesCount] = [];
        seriesNames[seriesCount] = seriesName;
        seriesCount++;
    }   
    let seriesIdx = seriesNames.indexOf(seriesName);
    series[seriesIdx].push({ "x": getTimestamp(row), "y": getValue(row) })
}

//prepare payload
msg.payload = [
    {
        "series": seriesNames,
        "data": series,
        "labels": [""]
    }
];

return msg;

Output...

Demo flow...

[{"id":"a58c3dce.c8a8d","type":"inject","z":"553814a2.1248ec","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timestamp\":\"2020-11-18 18:13:49.943\",\"device\":\"8b3790\",\"sensorname\":\"humidity\",\"value\":40.2},{\"timestamp\":\"2020-11-18 18:13:50.943\",\"device\":\"8b3790\",\"sensorname\":\"temperature\",\"value\":21.6},{\"timestamp\":\"2020-11-18 18:14:06.429\",\"device\":\"8b3790\",\"sensorname\":\"temperature\",\"value\":21.7},{\"timestamp\":\"2020-11-18 18:14:08.429\",\"device\":\"8b3790\",\"sensorname\":\"humidity\",\"value\":40.4},{\"timestamp\":\"2020-11-18 18:16:22.957\",\"device\":\"8b3790\",\"sensorname\":\"humidity\",\"value\":42.9},{\"timestamp\":\"2020-11-18 18:16:23.957\",\"device\":\"8b3790\",\"sensorname\":\"temperature\",\"value\":21.6},{\"timestamp\":\"2020-11-18 18:25:48.906\",\"device\":\"8b3790\",\"sensorname\":\"temperature\",\"value\":21.3},{\"timestamp\":\"2020-11-18 18:25:59.906\",\"device\":\"8b3790\",\"sensorname\":\"humidity\",\"value\":45.1}]","payloadType":"json","x":490,"y":3180,"wires":[["4523d0cb.fc9cf"]]},{"id":"4523d0cb.fc9cf","type":"function","z":"553814a2.1248ec","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:38\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"humidity\",\n        \"value\": 37.02\n    },\n    {\n        \"Timestamp\": \"2020-11-18 18:25:46\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"temperature\",\n        \"value\": 123\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"device/sensorname\", \"device/sensorname\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//helper functions\nconst getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch\nconst getValue = (e) => e.value //returns the value field\nconst getSeriesName = (e) => e.device + \"/\" + e.sensorname //generate a key / series name e.g. \"device/sensorname\"\n\n//build graph data\nlet series = [];\nlet seriesNames = [];\nlet seriesCount = 0;\nconst data = msg.payload;\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    const seriesName = getSeriesName(row);\n    if (seriesNames.includes(seriesName) == false) {\n        series[seriesCount] = [];\n        seriesNames[seriesCount] = seriesName;\n        seriesCount++;\n    }   \n    let seriesIdx = seriesNames.indexOf(seriesName);\n    series[seriesIdx].push({ \"x\": getTimestamp(row), \"y\": getValue(row) })\n}\n\n//prepare payload\nmsg.payload = [\n    {\n        \"series\": seriesNames,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":720,"y":3180,"wires":[["4e0e8d5d.f411a4","8ff8bd0a.1bd17"]]},{"id":"4e0e8d5d.f411a4","type":"debug","z":"553814a2.1248ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":980,"y":3180,"wires":[]},{"id":"8ff8bd0a.1bd17","type":"ui_chart","z":"553814a2.1248ec","name":"","group":"dce9e7a2.d20c78","order":4,"width":0,"height":0,"label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":990,"y":3240,"wires":[[]]},{"id":"dce9e7a2.d20c78","type":"ui_group","name":"Object detection","tab":"5132060d.4cde48","order":1,"disp":true,"width":"7","collapse":false},{"id":"5132060d.4cde48","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

[Harakaio]

WOW!
Nice - that works perfectly for one sensor. Really nice and cool!

For more sensor's I'm having the problem, that in my flow after your function node there were sent 4 different standalone messages in about 1 sec, what restarts the flow and does not display them in one chart.
Here is my flow:

[{"id":"dd06a909.e84d28","type":"function","z":"7783ee4a.75705","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 timestamp = d.getTime();\nvar fromdate = timestamp - Day;\nvar enddate = timestamp;\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 = timestamp-Day;\n                        enddate = timestamp;\n                        break;\n                    case \"yesterday\":\n                        fromdate = timestamp-2*Day;\n                        enddate = timestamp-Day;\n                        break;\n                    case \"week\":\n                        fromdate = timestamp-Week;\n                        enddate = timestamp;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM DATA WHERE device='\"+parts[0]+\"' AND sensorname='\"+parts[1]+\"' AND timestamp >= \" + fromdate + \" AND timestamp <= \" + 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,"noerr":0,"initialize":"","finalize":"","x":310,"y":1020,"wires":[["c411fc78.dabd9","91e8f4b7.b873f8"]]},{"id":"9f2c80f0.25273","type":"ui_form","z":"7783ee4a.75705","name":"Data points","label":"","group":"51e1679f.558fe8","order":2,"width":0,"height":0,"options":[{"label":"Zeitraum: heute","value":"period/today","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: gestern","value":"period/yesterday","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: diese Woche","value":"period/week","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftfeuchte","value":"device1/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Temperatur","value":"device1/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftdruck","value":"device1/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftfeuchte","value":"device2/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Temperatur","value":"device2/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftdruck","value":"device2/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftfeuchte","value":"device3/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Temperatur","value":"device3/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftdruck","value":"device3/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftfeuchte","value":"device4/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Temperatur","value":"device4/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftdruck","value":"device4/pressure","type":"checkbox","required":false,"rows":null}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"device1/humidity":false,"device1/temperature":false,"device1/pressure":false,"device2/humidity":false,"device2/temperature":false,"device2/pressure":false,"device3/humidity":false,"device3/temperature":false,"device3/pressure":false,"device4/humidity":false,"device4/temperature":false,"device4/pressure":false},"payload":"","submit":"send","cancel":"stop","topic":"","topicType":"str","splitLayout":false,"x":130,"y":1020,"wires":[["8e09d5fa.cfe388","dd06a909.e84d28"]]},{"id":"8e09d5fa.cfe388","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":310,"y":940,"wires":[]},{"id":"c411fc78.dabd9","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":940,"wires":[]},{"id":"91e8f4b7.b873f8","type":"change","z":"7783ee4a.75705","name":"device","rules":[{"t":"change","p":"topic","pt":"msg","from":"device1","fromt":"str","to":"8b3790","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device2","fromt":"str","to":"7d945d","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device3","fromt":"str","to":"8b39b7","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device4","fromt":"str","to":"7d9ba3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":1020,"wires":[["3f81ad79.b5ac92","a2f94e78.758ad"]]},{"id":"3f81ad79.b5ac92","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":630,"y":940,"wires":[]},{"id":"a2f94e78.758ad","type":"sqlite","z":"7783ee4a.75705","mydb":"8274941a.73344","sqlquery":"msg.topic","sql":"","name":"","x":710,"y":1020,"wires":[["23de8469.09a0dc","4523d0cb.fc9cf"]]},{"id":"23de8469.09a0dc","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":890,"y":940,"wires":[]},{"id":"4523d0cb.fc9cf","type":"function","z":"7783ee4a.75705","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:38\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"humidity\",\n        \"value\": 37.02\n    },\n    {\n        \"Timestamp\": \"2020-11-18 18:25:46\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"temperature\",\n        \"value\": 123\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"device/sensorname\", \"device/sensorname\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//helper functions\nconst getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch\nconst getValue = (e) => e.value //returns the value field\nconst getSeriesName = (e) => e.device + \"/\" + e.sensorname //generate a key / series name e.g. \"device/sensorname\"\n\n//build graph data\nlet series = [];\nlet seriesNames = [];\nlet seriesCount = 0;\nconst data = msg.payload;\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    const seriesName = getSeriesName(row);\n    if (seriesNames.includes(seriesName) == false) {\n        series[seriesCount] = [];\n        seriesNames[seriesCount] = seriesName;\n        seriesCount++;\n    }   \n    let seriesIdx = seriesNames.indexOf(seriesName);\n    series[seriesIdx].push({ \"x\": getTimestamp(row), \"y\": getValue(row) })\n}\n\n//prepare payload\nmsg.payload = [\n    {\n        \"series\": seriesNames,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1060,"y":1020,"wires":[["4e0e8d5d.f411a4","8ff8bd0a.1bd17"]]},{"id":"4e0e8d5d.f411a4","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1300,"y":1020,"wires":[]},{"id":"8ff8bd0a.1bd17","type":"ui_chart","z":"7783ee4a.75705","name":"","group":"ffc23bf3.cc9cf8","order":4,"width":0,"height":0,"label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1330,"y":1080,"wires":[[]]},{"id":"51e1679f.558fe8","type":"ui_group","name":"Selection","tab":"8edd66b5.883ed8","order":1,"disp":true,"width":"6","collapse":false},{"id":"8274941a.73344","type":"sqlitedb","db":"/opt/sqlite/wetterdatenbank.db","mode":"RW"},{"id":"ffc23bf3.cc9cf8","type":"ui_group","name":"Report","tab":"8edd66b5.883ed8","order":2,"disp":true,"width":"6","collapse":false},{"id":"8edd66b5.883ed8","type":"ui_tab","name":"sqliteReport","icon":"dashboard","order":9,"disabled":false,"hidden":false}]

[Steve-Mcl]

if you send 4 messages, the last one will be what populates the charts.

Are you expecting the 4 outputs from your SQL function to be all displayed on one graph?

That doesnt make a lot of sense.

[Harakaio]

let's assume that the user is able to select multiple sensor values from the datapoint:

this is my flow:

flow export:

[{"id":"dd06a909.e84d28","type":"function","z":"7783ee4a.75705","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 timestamp = d.getTime();\nvar fromdate = timestamp - Day;\nvar enddate = timestamp;\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 = timestamp-Day;\n                        enddate = timestamp;\n                        break;\n                    case \"yesterday\":\n                        fromdate = timestamp-2*Day;\n                        enddate = timestamp-Day;\n                        break;\n                    case \"week\":\n                        fromdate = timestamp-Week;\n                        enddate = timestamp;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM DATA WHERE device='\"+parts[0]+\"' AND sensorname='\"+parts[1]+\"' AND timestamp >= \" + fromdate + \" AND timestamp <= \" + 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,"noerr":0,"initialize":"","finalize":"","x":310,"y":1020,"wires":[["c411fc78.dabd9","91e8f4b7.b873f8"]]},{"id":"9f2c80f0.25273","type":"ui_form","z":"7783ee4a.75705","name":"Data points","label":"","group":"51e1679f.558fe8","order":2,"width":0,"height":0,"options":[{"label":"Zeitraum: heute","value":"period/today","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: gestern","value":"period/yesterday","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: diese Woche","value":"period/week","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftfeuchte","value":"device1/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Temperatur","value":"device1/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftdruck","value":"device1/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftfeuchte","value":"device2/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Temperatur","value":"device2/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftdruck","value":"device2/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftfeuchte","value":"device3/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Temperatur","value":"device3/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftdruck","value":"device3/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftfeuchte","value":"device4/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Temperatur","value":"device4/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftdruck","value":"device4/pressure","type":"checkbox","required":false,"rows":null}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"device1/humidity":false,"device1/temperature":false,"device1/pressure":false,"device2/humidity":false,"device2/temperature":false,"device2/pressure":false,"device3/humidity":false,"device3/temperature":false,"device3/pressure":false,"device4/humidity":false,"device4/temperature":false,"device4/pressure":false},"payload":"","submit":"send","cancel":"stop","topic":"","topicType":"str","splitLayout":false,"x":130,"y":1020,"wires":[["8e09d5fa.cfe388","dd06a909.e84d28"]]},{"id":"8e09d5fa.cfe388","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":150,"y":940,"wires":[]},{"id":"c411fc78.dabd9","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":310,"y":940,"wires":[]},{"id":"91e8f4b7.b873f8","type":"change","z":"7783ee4a.75705","name":"device","rules":[{"t":"change","p":"topic","pt":"msg","from":"device1","fromt":"str","to":"8b3790","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device2","fromt":"str","to":"7d945d","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device3","fromt":"str","to":"8b39b7","tot":"str"},{"t":"change","p":"topic","pt":"msg","from":"device4","fromt":"str","to":"7d9ba3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":1020,"wires":[["3f81ad79.b5ac92","a2f94e78.758ad"]]},{"id":"3f81ad79.b5ac92","type":"debug","z":"7783ee4a.75705","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":940,"wires":[]},{"id":"a2f94e78.758ad","type":"sqlite","z":"7783ee4a.75705","mydb":"8274941a.73344","sqlquery":"msg.topic","sql":"","name":"","x":710,"y":1020,"wires":[["23de8469.09a0dc","8e22c79a.79c988"]]},{"id":"23de8469.09a0dc","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":940,"wires":[]},{"id":"4523d0cb.fc9cf","type":"function","z":"7783ee4a.75705","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:38\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"humidity\",\n        \"value\": 37.02\n    },\n    {\n        \"Timestamp\": \"2020-11-18 18:25:46\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"temperature\",\n        \"value\": 123\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"device/sensorname\", \"device/sensorname\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//helper functions\nconst getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch\nconst getValue = (e) => e.value //returns the value field\nconst getSeriesName = (e) => e.device + \"/\" + e.sensorname //generate a key / series name e.g. \"device/sensorname\"\n\n//build graph data\nlet series = [];\nlet seriesNames = [];\nlet seriesCount = 0;\nconst data = msg.payload;\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    const seriesName = getSeriesName(row);\n    if (seriesNames.includes(seriesName) == false) {\n        series[seriesCount] = [];\n        seriesNames[seriesCount] = seriesName;\n        seriesCount++;\n    }   \n    let seriesIdx = seriesNames.indexOf(seriesName);\n    series[seriesIdx].push({ \"x\": getTimestamp(row), \"y\": getValue(row) })\n}\n\n//prepare payload\nmsg.payload = [\n    {\n        \"series\": seriesNames,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1200,"y":1020,"wires":[["4e0e8d5d.f411a4","8ff8bd0a.1bd17"]]},{"id":"4e0e8d5d.f411a4","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1250,"y":940,"wires":[]},{"id":"8ff8bd0a.1bd17","type":"ui_chart","z":"7783ee4a.75705","name":"","group":"ffc23bf3.cc9cf8","order":4,"width":0,"height":0,"label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1450,"y":1020,"wires":[[]]},{"id":"8e22c79a.79c988","type":"join","z":"7783ee4a.75705","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","timeout":"","count":"","x":970,"y":1020,"wires":[["4523d0cb.fc9cf","942c824f.e5fd7"]]},{"id":"942c824f.e5fd7","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":970,"y":940,"wires":[]},{"id":"51e1679f.558fe8","type":"ui_group","name":"Selection","tab":"8edd66b5.883ed8","order":1,"disp":true,"width":"6","collapse":false},{"id":"8274941a.73344","type":"sqlitedb","db":"/opt/sqlite/wetterdatenbank.db","mode":"RW"},{"id":"ffc23bf3.cc9cf8","type":"ui_group","name":"Report","tab":"8edd66b5.883ed8","order":2,"disp":true,"width":"6","collapse":false},{"id":"8edd66b5.883ed8","type":"ui_tab","name":"sqliteReport","icon":"dashboard","order":9,"disabled":false,"hidden":false}]

so in my opinion it should be possible to grant the user the right to select the possibility which sensor he/she wants to be displayed in the chart.
for each device/sensor combination there is an array, e . g. in this example for the four humidity sensors:

Dear Steve, could you please be so kind to me and add the function of a loop to your function node to work with the data?

this is an abstract of the output from the debug-node (full object):

{"topic":"SELECT * FROM DATA WHERE device='7d9ba3' AND sensorname='humidity' AND timestamp >= 1620292289168 AND timestamp <= 1620378689168","_msgid":"9baa64a2.384438","payload":[[{"timestamp":1620292338323,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292398324,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292458326,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292518342,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292578545,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292638551,"device":"7d945d","sensorname":"humidity","value":34.2},{"timestamp":1620292698551,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620292758568,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620292818568,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620292878584,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620292938585,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620292998615,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620293058616,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620293118616,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620293178615,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620293238618,"device":"7d945d","sensorname":"humidity","value":33.96},{"timestamp":1620293298618,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293358621,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293418622,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293478643,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293538643,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293598643,"device":"7d945d","sensorname":"humidity","value":33.97},{"timestamp":1620293658645,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620293718644,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620293778645,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620293838645,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620293898645,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620293958656,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620294018656,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620294078676,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620294138676,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620294198701,"device":"7d945d","sensorname":"humidity","value":33.94},{"timestamp":1620294258701,"device":"7d945d","sensorname":"humidity","value":33.94},[{"timestamp":1620292338323,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292398324,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292458326,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292518342,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292578545,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292638551,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292698551,"device":"8b3790","sensorname":"humidity","value":34.09},{"timestamp":1620292758568,"device":"8b3790","sensorname":"humidity","value":33.89},{"timestamp":1620292818568,"device":"8b3790","sensorname":"humidity","value":33.89},{"timestamp":1620292878584,"device":"8b3790","sensorname":"humidity","value":33.89},{"timestamp":1620292938585,"device":"8b3790","sensorname":"humidity","value":33.89},{"timestamp":1620292998615,"device":"8b3790","sensorname":"humidity","value":33.89},{"timestamp":1620293058616,"device":"8b3790","sensorname":"humidity","value":33.76},{"timestamp":1620293118616,"device":"8b3790","sensorname":"humidity","value":33.76},{"timestamp":1620293178615,"device":"8b3790","sensorname":"humidity","value":33.76},{"timestamp":1620293238618,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293298618,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293358621,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293418622,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293478643,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293538643,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293598643,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293658645,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293718644,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293778645,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293838645,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293898645,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620293958656,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620294018656,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620294078676,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620294138676,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620294198701,"device":"8b3790","sensorname":"humidity","value":33.83},{"timestamp":1620294258701,"device":"8b3790","sensorname":"humidity","value":33.83}],		[{"timestamp":1620292338323,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292398324,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292458326,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292518342,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292578545,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292638551,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292698551,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292758568,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292818568,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292878584,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292938585,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620292998615,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620293058616,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620293118616,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620293178615,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620293238618,"device":"8b39b7","sensorname":"humidity","value":33.56},{"timestamp":1620293298618,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293358621,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293418622,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293478643,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293538643,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293598643,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293658645,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293718644,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293778645,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293838645,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293898645,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620293958656,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620294018656,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620294078676,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620294138676,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620294198701,"device":"8b39b7","sensorname":"humidity","value":33.51},{"timestamp":1620294258701,"device":"8b39b7","sensorname":"humidity","value":33.35}],		[{"timestamp":1620292338323,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292398324,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292458326,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292518342,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292578545,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292638551,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292698551,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292758568,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292818568,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292878584,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292938585,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620292998615,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293058616,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293118616,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293178615,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293238618,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293298618,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293358621,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293418622,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293478643,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293538643,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293598643,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293658645,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293718644,"device":"7d9ba3","sensorname":"humidity","value":33.48},{"timestamp":1620293778645,"device":"7d9ba3","sensorname":"humidity","value":33.38},{"timestamp":1620293838645,"device":"7d9ba3","sensorname":"humidity","value":33.38},{"timestamp":1620293898645,"device":"7d9ba3","sensorname":"humidity","value":33.38},{"timestamp":1620293958656,"device":"7d9ba3","sensorname":"humidity","value":33.38},{"timestamp":1620294018656,"device":"7d9ba3","sensorname":"humidity","value":33.35},{"timestamp":1620294078676,"device":"7d9ba3","sensorname":"humidity","value":33.35},{"timestamp":1620294138676,"device":"7d9ba3","sensorname":"humidity","value":33.35},{"timestamp":1620294198701,"device":"7d9ba3","sensorname":"humidity","value":33.35},{"timestamp":1620294258701,"device":"7d9ba3","sensorname":"humidity","value":33.35}]]}

[Steve-Mcl]

The problem is you are looking at this from the wrong angle. Instead of making 4 queries (slow!) and then combining them in JS (slow!) just grab all data at once (fast!)

As i said before...

@Steve-Mcl

your query will need to be such that it returns the items of interest in one go

So instead of doing 4 separate queries like these...
SELECT * FROM DATA WHERE device='7d945d' AND sensorname='humidity' AND timestamp >= 1620292289168 AND timestamp <= 1620378689168
SELECT * FROM DATA WHERE device='8b3790' AND sensorname='humidity' AND timestamp >= 1620292289168 AND timestamp <= 1620378689168
SELECT * FROM DATA WHERE device='8b39b7' AND sensorname='humidity' AND timestamp >= 1620292289168 AND timestamp <= 1620378689168
SELECT * FROM DATA WHERE device='7d9ba3' AND sensorname='humidity' AND timestamp >= 1620292289168 AND timestamp <= 1620378689168

Your SQL function should be returning ONE query like ...

SELECT * FROM DATA 
WHERE  (timestamp >= 1620292289168 AND timestamp <= 1620378689168) AND
       (   
             (device='7d945d' AND sensorname='humidity') OR
             (device='8b3790' AND sensorname='humidity') OR
             (device='8b39b7' AND sensorname='humidity') OR
             (device='7d9ba3' AND sensorname='humidity') 
       )
ORDER BY timestamp asc

If you can do that ↑ then the it should just work - (the "DB Data to Chart Data" function was written to be dynamic).

Try it using that hand crafted query ...

[{"id":"57d07816.f643b8","type":"debug","z":"553814a2.1248ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1130,"y":3620,"wires":[]},{"id":"e1bbd7fb.e94fb8","type":"function","z":"553814a2.1248ec","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:38\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"humidity\",\n        \"value\": 37.02\n    },\n    {\n        \"Timestamp\": \"2020-11-18 18:25:46\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"temperature\",\n        \"value\": 123\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"device/sensorname\", \"device/sensorname\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//helper functions\nconst getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch\nconst getValue = (e) => e.value //returns the value field\nconst getSeriesName = (e) => e.device + \"/\" + e.sensorname //generate a key / series name e.g. \"device/sensorname\"\n\n//build graph data\nlet series = [];\nlet seriesNames = [];\nlet seriesCount = 0;\nconst data = msg.payload;\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    const seriesName = getSeriesName(row);\n    if (seriesNames.includes(seriesName) == false) {\n        series[seriesCount] = [];\n        seriesNames[seriesCount] = seriesName;\n        seriesCount++;\n    }   \n    let seriesIdx = seriesNames.indexOf(seriesName);\n    series[seriesIdx].push({ \"x\": getTimestamp(row), \"y\": getValue(row) })\n}\n\n//prepare payload\nmsg.payload = [\n    {\n        \"series\": seriesNames,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1080,"y":3580,"wires":[["57d07816.f643b8","a5cc779.640a988"]]},{"id":"a112d780.75cc38","type":"sqlite","z":"553814a2.1248ec","mydb":"5711f752.d7fe68","sqlquery":"msg.topic","sql":"","name":"","x":810,"y":3580,"wires":[["bd4c8bb5.6ea038","e1bbd7fb.e94fb8"]]},{"id":"a5cc779.640a988","type":"ui_chart","z":"553814a2.1248ec","name":"","group":"b2fc1fcb.c5d9b","order":4,"width":0,"height":0,"label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1290,"y":3580,"wires":[[]]},{"id":"eee80143.1f7b3","type":"template","z":"553814a2.1248ec","name":"test query","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"SELECT * FROM DATA \nWHERE  (timestamp >= 1620292289168 AND timestamp <= 1620378689168) AND\n       (   \n             (device='7d945d' AND sensorname='humidity') OR\n             (device='8b3790' AND sensorname='humidity') OR\n             (device='8b39b7' AND sensorname='humidity') OR\n             (device='7d9ba3' AND sensorname='humidity') \n       )\nORDER BY timestamp asc","output":"str","x":560,"y":3580,"wires":[["a112d780.75cc38","c9f5a68.a7cc858"]]},{"id":"bd4c8bb5.6ea038","type":"debug","z":"553814a2.1248ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":870,"y":3620,"wires":[]},{"id":"59293317.0a003c","type":"inject","z":"553814a2.1248ec","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":400,"y":3580,"wires":[["eee80143.1f7b3"]]},{"id":"c9f5a68.a7cc858","type":"debug","z":"553814a2.1248ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"topic","statusType":"auto","x":580,"y":3620,"wires":[]},{"id":"5711f752.d7fe68","type":"sqlitedb","db":"/opt/sqlite/wetterdatenbank.db","mode":"RW"},{"id":"b2fc1fcb.c5d9b","type":"ui_group","name":"Report","tab":"31f56ba8.ffee64","order":2,"disp":true,"width":"6","collapse":false},{"id":"31f56ba8.ffee64","type":"ui_tab","name":"sqliteReport","icon":"dashboard","order":9,"disabled":false,"hidden":false}]

... does it work?

If it ↑ does, then you simply need to build that query in your "SQL" function and return ONE msg with the query in the topic.

[Harakaio]

hey together

thanks for the incredible support and a big merci to steve!
I have rebuilt and modularized the SQL query (hardcoded for now); it works now as desired!

here is my flow right now:

in bb-code:

[{"id":"a046b39e.fea15","type":"function","z":"7783ee4a.75705","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 timestamp = d.getTime();\nvar fromdate = timestamp - Day;\nvar enddate = timestamp;\nvar output = [];\n\nvar SQLbase = \"Select * from DATA\";\n\nmsg.topic = SQLbase;\n\n/* (Zigbee2Mqtt Sensoren-Bezeichner)\nzigbee2mqtt/8b3790\nzigbee2mqtt/7d945d\nzigbee2mqtt/8b39b7\nzigbee2mqtt/7d9ba3\n*/\n\n\nconst devsensorArray = [\n    msg.payload[\"device1/humidity\"] == true,\n    msg.payload[\"device1/temperature\"] == true,\n    msg.payload[\"device1/pressure\"] == true,\n    msg.payload[\"device2/humidity\"] == true,\n    msg.payload[\"device2/temperature\"] == true,\n    msg.payload[\"device2/pressure\"] == true,\n    msg.payload[\"device3/humidity\"] == true,\n    msg.payload[\"device3/temperature\"] == true,\n    msg.payload[\"device3/pressure\"] == true,\n    msg.payload[\"device4/humidity\"] == true,\n    msg.payload[\"device4/temperature\"] == true,\n    msg.payload[\"device4/pressure\"] == true,\n]\n\n//Count positive (true) members in const-Array:\nfunction numberOfTrueValues(obj) {\n    var counter = 0;\n    for (var index = 0; index < obj.length; index++) {\n        if (obj[index].payload === true) {\n         counter++;\n        }\n   }\n    return counter;\n}\n\n\n\nvar SQLdevBegin = \" AND (\";\nvar SQLDelimiter =\"\";\nvar SQLdevEnd = \")\";\n\nvar SQLdev1hum = \"(device = '8b3790' AND sensorname = 'humidity')\";\nvar SQLdev1temp = \"(device = '8b3790' AND sensorname = 'temperature')\";\nvar SQLdev1pres = \"(device = '8b3790' AND sensorname = 'pressure')\";\nvar SQLdev2hum = \"(device = '7d945d' AND sensorname = 'humidity')\";\nvar SQLdev2temp = \"(device = '7d945d' AND sensorname = 'temperature')\";\nvar SQLdev2pres = \"(device = '7d945d' AND sensorname = 'pressure')\";\nvar SQLdev3hum = \"(device = '8b39b7' AND sensorname = 'humidity')\";\nvar SQLdev3temp = \"(device = '8b39b7' AND sensorname = 'temperature')\";\nvar SQLdev3pres = \"(device = '8b39b7' AND sensorname = 'pressure')\";\nvar SQLdev4hum = \"(device = '7d9ba3' AND sensorname = 'humidity')\";\nvar SQLdev4temp = \"(device = '7d9ba3' AND sensorname = 'temperature')\";\nvar SQLdev4pres = \"(device = '7d9ba3' AND sensorname = 'pressure')\";\n\n\n//SQL-Schleife für den Zeitstempel\nif (msg.payload[\"period/today\"] == true){\n    fromdate = timestamp-Day;\n    enddate = timestamp;\n    msg.topic = msg.topic + \" WHERE (timestamp >= \"+fromdate+\" AND timestamp <= \"+enddate+\" )\";\n}\nif (msg.payload[\"period/yesterday\"] == true){\n    fromdate = timestamp-2*Day;\n    enddate = timestamp-Day;\n    msg.topic = msg.topic + \" WHERE (timestamp >= \"+fromdate+\" AND timestamp <= \"+enddate+\" )\";\n}\nif (msg.payload[\"period/week\"] == true){\n    fromdate = timestamp-Week;\n    enddate = timestamp;\n    msg.topic = msg.topic + \" WHERE (timestamp >= \"+fromdate+\" AND timestamp <= \"+enddate+\" )\";\n}\n\n//SQL-Abfrage für die Sensoren; damit der erste Part in die SQL-Abfrage übernommen werden kann (Beginn)\n\n\n//Check if Sensor's are selected and put the SQLdevbegin and SQLdevend\nif (devsensorArray.includes(true)) {\n    msg.topic = msg.topic + SQLdevBegin;\n}\n\n/***\n//check, if multiple sensors are choosen\nif (numberOfTrueValues(devsensorArray) > 1) {\n    SQLDelimiter = \" OR \";\n}\n***/\n\nif (msg.payload[\"device1/humidity\"] == true){\n    msg.topic = msg.topic + SQLdev1hum + SQLDelimiter;\n}\n\nif (msg.payload[\"device1/temperature\"] == true){\n    msg.topic = msg.topic + SQLdev1temp + SQLDelimiter;\n}\n\nif (msg.payload[\"device1/pressure\"] == true){\n    msg.topic = msg.topic + SQLdev1pres + SQLDelimiter;\n}\n\n\nif (msg.payload[\"device2/humidity\"] == true){\n    msg.topic = msg.topic + SQLdev2hum + SQLDelimiter;\n}\n\nif (msg.payload[\"device2/temperature\"] == true){\n    msg.topic = msg.topic + SQLdev2temp + SQLDelimiter;\n}\n\nif (msg.payload[\"device2/pressure\"] == true){\n    msg.topic = msg.topic + SQLdev2pres + SQLDelimiter;\n}\n\n\nif (msg.payload[\"device3/humidity\"] == true){\n    msg.topic = msg.topic + SQLdev3hum + SQLDelimiter;\n}\n\nif (msg.payload[\"device3/temperature\"] == true){\n    msg.topic = msg.topic + SQLdev3temp + SQLDelimiter;\n}\n\nif (msg.payload[\"device3/pressure\"] == true){\n    msg.topic = msg.topic + SQLdev3pres + SQLDelimiter;\n}\n\n\nif (msg.payload[\"device4/humidity\"] == true){\n    msg.topic = msg.topic + SQLdev4hum + SQLDelimiter;\n}\n\nif (msg.payload[\"device4/temperature\"] == true){\n    msg.topic = msg.topic + SQLdev4temp + SQLDelimiter;\n}\n\nif (msg.payload[\"device4/pressure\"] == true){\n    msg.topic = msg.topic + SQLdev4pres + SQLDelimiter;\n}\n\n\n\n//SQL-Abfrage für die Sensoren; damit der letzte Part in die SQL-Abfrage übernommen werden kann (Ende)\n//Check if Sensor's are selected and put the SQLdevbegin and SQLdevend\nif (devsensorArray.includes(true)) {\n    msg.topic = msg.topic + SQLdevEnd;\n}\n\n\n\n\n\nreturn [msg]\n\n\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 = timestamp-Day;\n                        enddate = timestamp;\n                        break;\n                    case \"yesterday\":\n                        fromdate = timestamp-2*Day;\n                        enddate = timestamp-Day;\n                        break;\n                    case \"week\":\n                        fromdate = timestamp-Week;\n                        enddate = timestamp;\n                        break;\n                }\n            } else {\n                output.push({ topic: \"SELECT * FROM DATA WHERE device='\"+parts[0]+\"' AND sensorname='\"+parts[1]+\"' AND timestamp >= \" + fromdate + \" AND timestamp <= \" + 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,"noerr":0,"initialize":"","finalize":"","x":310,"y":1140,"wires":[["343df201.ad0b1e","23e5f7db.493548"]]},{"id":"a213b1ca.42ec2","type":"inject","z":"7783ee4a.75705","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"period/today\":true,\"period/yesterday\":false,\"period/week\":false,\"device1/humidity\":true,\"device1/temperature\":false,\"device1/pressure\":false,\"device2/humidity\":false,\"device2/temperature\":false,\"device2/pressure\":false,\"device3/humidity\":false,\"device3/temperature\":false,\"device3/pressure\":false,\"device4/humidity\":false,\"device4/temperature\":false,\"device4/pressure\":false}","payloadType":"json","x":140,"y":1200,"wires":[["a046b39e.fea15","8c52397c.c4a4d8"]]},{"id":"343df201.ad0b1e","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":1080,"wires":[]},{"id":"8c52397c.c4a4d8","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":310,"y":1200,"wires":[]},{"id":"2d6d1193.120f5e","type":"ui_form","z":"7783ee4a.75705","name":"Data points","label":"DATAPOINTS (2)","group":"51e1679f.558fe8","order":2,"width":0,"height":0,"options":[{"label":"Zeitraum: heute","value":"period/today","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: gestern","value":"period/yesterday","type":"checkbox","required":false,"rows":null},{"label":"Zeitraum: diese Woche","value":"period/week","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftfeuchte","value":"device1/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Temperatur","value":"device1/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 1 / Luftdruck","value":"device1/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftfeuchte","value":"device2/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Temperatur","value":"device2/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 2 / Luftdruck","value":"device2/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftfeuchte","value":"device3/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Temperatur","value":"device3/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 3 / Luftdruck","value":"device3/pressure","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftfeuchte","value":"device4/humidity","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Temperatur","value":"device4/temperature","type":"checkbox","required":false,"rows":null},{"label":"Device 4 / Luftdruck","value":"device4/pressure","type":"checkbox","required":false,"rows":null}],"formValue":{"period/today":false,"period/yesterday":false,"period/week":false,"device1/humidity":false,"device1/temperature":false,"device1/pressure":false,"device2/humidity":false,"device2/temperature":false,"device2/pressure":false,"device3/humidity":false,"device3/temperature":false,"device3/pressure":false,"device4/humidity":false,"device4/temperature":false,"device4/pressure":false},"payload":"","submit":"start","cancel":"abbrechen","topic":"","topicType":"str","splitLayout":false,"x":130,"y":1140,"wires":[["a046b39e.fea15","8c52397c.c4a4d8"]]},{"id":"23e5f7db.493548","type":"change","z":"7783ee4a.75705","name":"","rules":[{"t":"change","p":"topic","pt":"msg","from":"')(d","fromt":"str","to":"') OR (d","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":1140,"wires":[["8373ae33.439e","6b531658.5554f8"]]},{"id":"8373ae33.439e","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":690,"y":1080,"wires":[]},{"id":"6b531658.5554f8","type":"sqlite","z":"7783ee4a.75705","mydb":"8274941a.73344","sqlquery":"msg.topic","sql":"","name":"","x":830,"y":1140,"wires":[["a883269a.d352b8","fd6e893a.d7c4d8"]]},{"id":"a883269a.d352b8","type":"function","z":"7783ee4a.75705","name":"DB Data to Chart data","func":"/* DB Data\n[\n    {\n        \"Timestamp\": \"2020-11-18 18:25:38\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"humidity\",\n        \"value\": 37.02\n    },\n    {\n        \"Timestamp\": \"2020-11-18 18:25:46\",\n        \"device\":\"8b3790\",\n        \"sensorname\":\"temperature\",\n        \"value\": 123\n    },\n]\n*/\n/* Desired format\n[{\n\"series\": [\"device/sensorname\", \"device/sensorname\"],\n\"data\": [\n    [{ \"x\": 1504029632890, \"y\": 5 }, //series 1 data\n     { \"x\": 1504029636001, \"y\": 4 },\n    ],\n    [{ \"x\": 1504029633514, \"y\": 6 }, //series 2 data\n     { \"x\": 1504029636622, \"y\": 7 },\n    ],\n],\n\"labels\": [\"\"]\n}]\n*/\n\n//helper functions\nconst getTimestamp = (e) => { return new Date(e.timestamp).valueOf() } //verifies and returns epoch\nconst getValue = (e) => e.value //returns the value field\nconst getSeriesName = (e) => e.device + \"/\" + e.sensorname //generate a key / series name e.g. \"device/sensorname\"\n\n//build graph data\nlet series = [];\nlet seriesNames = [];\nlet seriesCount = 0;\nconst data = msg.payload;\n\n//loop each row and build an array in the required format\nfor (let index = 0; index < msg.payload.length; index++) {\n    const row = msg.payload[index];\n    const seriesName = getSeriesName(row);\n    if (seriesNames.includes(seriesName) == false) {\n        series[seriesCount] = [];\n        seriesNames[seriesCount] = seriesName;\n        seriesCount++;\n    }   \n    let seriesIdx = seriesNames.indexOf(seriesName);\n    series[seriesIdx].push({ \"x\": getTimestamp(row), \"y\": getValue(row) })\n}\n\n//prepare payload\nmsg.payload = [\n    {\n        \"series\": seriesNames,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1140,"y":1140,"wires":[["172f60fb.8a196f","74820095.166de"]]},{"id":"172f60fb.8a196f","type":"ui_chart","z":"7783ee4a.75705","name":"","group":"ffc23bf3.cc9cf8","order":4,"width":0,"height":0,"label":"sqlite data","chartType":"line","legend":"true","xformat":"auto","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1380,"y":1140,"wires":[["60c59e2d.bfe0e"]]},{"id":"fd6e893a.d7c4d8","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1010,"y":1080,"wires":[]},{"id":"74820095.166de","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1270,"y":1080,"wires":[]},{"id":"60c59e2d.bfe0e","type":"debug","z":"7783ee4a.75705","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1530,"y":1080,"wires":[]},{"id":"51e1679f.558fe8","type":"ui_group","name":"Selection","tab":"8edd66b5.883ed8","order":1,"disp":true,"width":"6","collapse":false},{"id":"8274941a.73344","type":"sqlitedb","db":"/opt/sqlite/wetterdatenbank.db","mode":"RW"},{"id":"ffc23bf3.cc9cf8","type":"ui_group","name":"Report","tab":"8edd66b5.883ed8","order":2,"disp":true,"width":"6","collapse":false},{"id":"8edd66b5.883ed8","type":"ui_tab","name":"sqliteReport","icon":"dashboard","order":9,"disabled":false,"hidden":false}]