Function node consume multiple msg.payloads with different topics

General
1

Back again with another NOOB question. I have a function that actions against multiple different payloads, but cannot figure out how to ID them uniquely within the function. And yes...I have gone through the standard documentation without success.

Simplified example:

Two timestamp inject nodes into a function node that acts upon each message/topic uniquely. How do I properly ID each input within the function? E.G.

timestamp #1 topic = 1
timestamp #2 topic=2

Within function:

let variable1 = new Date("what the heck do I put here? ); //to action against the payload within topic #1
let variable2 = new Date("what the heck do I put here? ); //to action against the payload within topic #2

2

If every msg has a different topic then
If (msg.topic == 'topic1'){
let payload1 = msg.payload;
}
If(msg.topic== 'topic2'){
let payload2 =msg.payload
}

If you need to store variables look at context or flow.set

3

Typing on small screen, be careful of typo's

4

Or set one payload to msg.pay1
one payload to msg.pay2
let payload1 = msg.pay1;
let payload2= msg.pay2;

5

Thank you for the quick feedback! I have tried several different variants, but so far no success. Here is additional detail:

Injecting first timestamp with topic: "tstart"
Injecting second timestamp with topic: "tend"

Into this function node:

function formatDate(date) {
var d = new Date(date),
month = '' + (d.getMonth() + 1),
day = '' + d.getDate(),
year = d.getFullYear();

if (month.length < 2) 
    month = '0' + month;
if (day.length < 2) 
    day = '0' + day;

return [year, month, day].join('-');

}

}
If(msg.topic== 'tstart'){
let payloadtstart =msg.payload
}

}
If(msg.topic== 'tend'){
let payloadtend =msg.payload
}

let dStart = new Date(payloadtstart);
//proof time is normal (its a timezone thing)
node.warn(["proof start date is ok - yyyy mm dd hh mm ss",
dStart.getFullYear(),
dStart.getMonth()+1,
dStart.getDate(),
dStart.getHours(),
dStart.getMinutes(),
dStart.getSeconds()
]);
msg.payload.startToString = dStart.toString();//default toString
msg.payload.startDateIsoformat = formatDate(dStart); //iso format date only

let dEnd = new Date(payloadtend);
//proof time is normal (its a timezone thing)
node.warn(["proof end date is ok - yyyy mm dd hh mm ss",
dEnd.getFullYear(),
dEnd.getMonth()+1,
dEnd.getDate(),
dEnd.getHours(),
dEnd.getMinutes(),
dEnd.getSeconds()
]);
msg.payload.endToString = dEnd.toString();//default toString
msg.payload.endDateIsoformat = formatDate(dEnd); //iso format date only
return msg;

Debug node from the function shows nothing/no output, catch all shows nothing.

6

you have extra curly braces above each of your if statements and also If should be lowercase if

ps. when you paste code better paste using image

it formats the code better

and its good in these cases to share your test flow (if possible) so we can have a better picture of how you inject your values.

7

you should really initialise your payloadstart and payloadend , otherwise the uninitialised value will be undefined when you try and generate a date from it.

also, check out Date.toISOString() to save you a fair number of lines of code

8

Thank you very much for the formatting of code guidance!

Well I have myself completely messed up now, and have decided to start from the beginning. My overarching goal is to use 2 UI_DATE_PICKER nodes to choose start and end dates that will be used within the SQL select statement after selecting the "Submit" button.

I can successfully get the date (timestamp for testing) in either of the following formats from various flows:

dates

For my DB query to function properly I need the dates in the select statement to be formatted as such:

SELECT *
FROM exterior_climate
WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'

Thus I need to somehow reformat either of the two messages shown above into the proper format for inclusion into the select statement. It looks like I may also have to accommodate for days and or months that only have a single digit from node-red. Simple? Apparently not for me.

Thanks in advance!

9

If you could post your flow we will do our best to help

10 
[{"id":"47fb3c99.06aedc","type":"debug","z":"1963c951.33dadf","name":"F","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":490,"y":120,"wires":[]}]

This flow shows how to get date and time formatted very easily, take a look and maybe it will help to get you started

11

it would be better if you shared your flow

@gerry you must have exported without selecting all the nodes ..
your flow consists only of a debug node :wink:

Test Flow using a form and a function node that uses JS to slice the useful part from the date and adds it with the time

[{"id":"f7b686e9.942c48","type":"ui_form","z":"3ba66233.9f3d6e","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":"","x":170,"y":160,"wires":[["75672d13.576b74"]]},{"id":"c07a65ac.e4f978","type":"debug","z":"3ba66233.9f3d6e","name":"to DB","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":160,"wires":[]},{"id":"75672d13.576b74","type":"function","z":"3ba66233.9f3d6e","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\n// SELECT * FROM exterior_climate WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'\nmsg.topic = `SELECT * FROM exterior_climate WHERE Timestamp BETWEEN '${startDateTime}' AND '${endDateTime}`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":370,"y":160,"wires":[["c07a65ac.e4f978"]]},{"id":"2fdee068.4551f8","type":"ui_group","name":"Select Date time range","tab":"b724c3ac.56f638","order":1,"disp":true,"width":"16","collapse":false},{"id":"b724c3ac.56f638","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
12

Apologies, since @UnborN has posted an example already I'll leave it at that

13

@UnborN This looks very elegant and simple compared to the other things I have been trying. Another NOOB question however, the select statement output from the function contains double quotes which don't work when passed to SQL and I am not skilled enough to figure out how to remove these.

"SELECT * FROM exterior_climate WHERE Timestamp BETWEEN '2020-12-01 07:00' AND '2020-12-02 06:00'"

Could you please offer guidance?

14

How are you determining that?

If you mean in the debug node, they're not actually in the string, just for representation in the debug side bar.

15

Steve...thank you, should have wired up the whole flow prior to asking the question...my bad. Thanks to you both for such and elegant and code-light solution. So much better than any of the other dozens that I have bee working through! Everything works great now.

In hopes that others may benefit from this solution I am posting the flow which includes a formatting function to output the data to a line graph that displays all 5 data points from the DB.

[{"id":"6e3ea82b.1fc4d8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"654d9a77.d5c3b4","type":"mysql","z":"6e3ea82b.1fc4d8","mydb":"d580992f.308f18","name":"MYSQL","x":580,"y":200,"wires":[["ee5dfd0f.14164","a5ba77b2.19fbd8"]]},{"id":"ee5dfd0f.14164","type":"debug","z":"6e3ea82b.1fc4d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":240,"wires":[]},{"id":"5ba7df0d.a25","type":"ui_form","z":"6e3ea82b.1fc4d8","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":"","x":150,"y":200,"wires":[["765cb185.0c279"]]},{"id":"efba769a.c10528","type":"debug","z":"6e3ea82b.1fc4d8","name":"to DB","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":370,"y":260,"wires":[]},{"id":"765cb185.0c279","type":"function","z":"6e3ea82b.1fc4d8","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\n// SELECT * FROM exterior_climate WHERE Timestamp BETWEEN '2020-12-01 11:08:13' AND '2020-12-01 16:05:13'\nmsg.topic = `SELECT * FROM exterior_climate WHERE Timestamp BETWEEN '${startDateTime}' AND '${endDateTime}'`\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":350,"y":200,"wires":[["efba769a.c10528","654d9a77.d5c3b4"]]},{"id":"a5ba77b2.19fbd8","type":"function","z":"6e3ea82b.1fc4d8","name":"DB Data to Chart data","func":"var series = [];\nvar data = msg.payload;\nif (!data || !Array.isArray(data) || !data.length) {\n    node.warn(\"expected an array of data with at least 1 element\");\n    return null;\n    //alternatively return the msg with a null payload to clear graph\n}\n\nconst tsField = \"Timestamp\";\nvar _f = Object.keys(data[0]);\nvar fields = [];\nvar fi = 0;\n_f.forEach(function(elem) {\n    if (elem != tsField) {\n        fields.push(elem);\n        series[fi++] = [];\n    }\n});\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    var t = new Date(row[tsField]).valueOf()\n    var i = 0;\n    for (let f = 0; f < fields.length; f++ ) {\n        let field = fields[f];\n        series[f].push({ \"x\": t, \"y\": row[field] })\n    }\n}\n\nmsg.payload = [\n    {\n        \"series\": fields,\n        \"data\": series,\n        \"labels\": [\"\"]\n    }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":780,"y":140,"wires":[["a01128b7.739a08","5e22f744.5c1148"]]},{"id":"5e22f744.5c1148","type":"ui_chart","z":"6e3ea82b.1fc4d8","name":"Historical","group":"2fdee068.4551f8","order":7,"width":"13","height":"10","label":"","chartType":"line","legend":"true","xformat":"dd-D","interpolate":"linear","nodata":"No Data yet...","dot":false,"ymin":"","ymax":"","removeOlder":"7","removeOlderPoints":"20160","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#ff0000","#00b31e","#2777b4","#ffe666","#2777b4","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":1020,"y":140,"wires":[[]]},{"id":"a01128b7.739a08","type":"debug","z":"6e3ea82b.1fc4d8","name":"DB Chart","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":780,"y":180,"wires":[]},{"id":"d580992f.308f18","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"mqtt_data","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":"b724c3ac.56f638","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
16

dates
dates1480Ă—879 118 KB

3 Likes
17

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.