User Input start date and stop date, and how to extract data from the sql according to dates

[ksthar]

Hello everyone,

I would like to ask something regarding to take out the data from the mySQL database.

• I took the start date and stop date with the date picker node.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
var date = new Date(msg.payload); // assuming msg.payload contains the epoch number
var dateString = date.toISOString().slice(0, 10);
msg.payload = null; // set the payload to null to remove it
msg.dateStart = dateString; // create a new property called "date" and assign the formatted date string
return msg; // return the modified message object
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

• This is the program for the message date start and date stop. Only msg.dateStart and msg.dateStop is different

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
var startDate = msg.dateStart;
var endDate = msg.dateStop;
var query = "SELECT * FROM sensor_data1 WHERE DATE(timestamp) BETWEEN '" + startDate + "' AND '" + endDate + "'";
msg.topic = query;
return msg;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

This is a query about how I am taking out data from the database.

And this is the photo of the debug window.

As you can see, even though I gave the start date and stop date from the date picker node, they are separated, and if I can not get them together. I already tried that query in mySQL and it works fine.

Can someone help me regarding these issues, if I can get the data between start date and stop date, I can get the data between these values and I can use the data to make forward steps.
I am very very new to Node-Red so if there are some errors that I've done stupidly, please mention them and let me know.

[zenofmud]

Not seeing your flow, I would add some node.warn() statements in the function nodes to see what you have in the variables.

Where does msg.date stop come from?
Also are you sending two msgs into a function node expecting them both to be accessible at the same time? They won’t be.

See Writing Functions : Node-RED

[marcus-j-davies]

@ksthar

they are separated

This is an all to common hurdle, that new users face.

On assumption you are using 2 stock date pickers

Messages in Node RED are NEVER delivered at the same time and are NEVER delivered together as one message (unless you design your flow to do so)

Therefore, you are seeing this:

Set Start Date -> Triggers your SQL (only start date is in the message)
Set End Date -> Triggers your SQL (only end date is in the message)

Every message that is travelling through Node RED - will trigger the nodes they pass.
So your SQL is being triggered twice (one for each change event)

what you need to do, is use a Join Node - to join the 2 date set events - that will then combine them into 1 message = 1 SQL execution with both values intact.

Obviously, configure the Join node to suite your needs

Messages in Node RED are stateless in terms of execution (normally) - so they are not aware of previous values being set. Again - every Message is a completely different piece of data - unattached to previous executions of the same flow path.

[E1cid]

Or use the ui-form which will deliver both start and end in same message.
e.g.

[{"id":"b4273a82c9b157fe","type":"ui_form","z":"65617ffeb779f51c","name":"","label":"","group":"2d4fe667.28f8ba","order":23,"width":0,"height":0,"options":[{"label":"Start Date","value":"start","type":"date","required":true,"rows":null},{"label":"End Date","value":"end","type":"date","required":true,"rows":null}],"formValue":{"start":"","end":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","className":"","x":150,"y":2720,"wires":[["393ca2f9fbe76805"]]},{"id":"393ca2f9fbe76805","type":"function","z":"65617ffeb779f51c","name":"function 22","func":"msg.topic =`SELECT * FROM sensor_data1 WHERE Date(timestamp) BETWEEN ${msg.payload.start.slice(0,10)} AND ${msg.payload.end.slice(0, 10)};`\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":2720,"wires":[["cfbc88af924298a7","b4273a82c9b157fe"]]},{"id":"cfbc88af924298a7","type":"debug","z":"65617ffeb779f51c","name":"debug 262","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":2720,"wires":[]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

[ksthar]

Sorry about the flow
As I am a new user, I can only put two pictures.
Here it is.

[Steve-Mcl]

Usually a picture is worth a thousand words however in this case, we cannot assist from a picture alone (as we dont know what you are doing in the function nodes)

I would suggest you use a join node OR the ui-form control.

Ultimately, you want 1 msg going into your "get data query" function and that msg's payload should have BOTH the start and the stop date in one single msg

See this article in the cookbook for an example of how to join messages into one object.

[ksthar]

Hi Marcus,

Thank you so much for your help.
Now it is working and I tried to use "join" yesterday.
But lack of knowledge of Node-Red, I can not combine it.

Again thank you so much.

[ksthar]

Hi Eicid,

It works for me too. I have already tried it and it works perfectly.
I'll use your suggestion to continue my project because it is short and straightforward.
Thank you so much for your time and your help for me.

Cheers !

[ksthar]

Hi Steve,

It is my apology that I can not upload more than two pictures because of my new face in this forum.....
I have already tried the solutions you've mentioned, and both perfectly fit me.

Thank you so much for your suggestions, too.

Cheers