Date Picker as Range Mysql Query

[irvan sandoval]

Hello, i'm on my project to create dashboard from Mysql data, i succeed to make visualize data by day, but i want to to using Datepicker for data range, this is what i achieve.

I addes two datepicker for "from" and "to" range, but i have no idea how to do connect it to my sql query, this is my sql query function

// This will handle any device and any attribute as long as it is in the DB

var Week  = 604800000 ; //7 Days

var Day   =  86400000 ; // 1 Days

var d = new Date();

var epoch = d.getTime();

var fromdate = epoch - Day;

var enddate = epoch;

var output = [];

for (var property in msg.payload) {

    if (msg.payload.hasOwnProperty(property)) {

        //output.push({ payload: property + " has value "+msg.payload[property] });

        if (msg.payload[property]) {

            var parts = property.split("/");

            if (parts[0]==="period") {

                switch (parts[1]) {

                    case "today":

                        fromdate = epoch-Day;

                        enddate = epoch;

                        break;

                    case "yesterday":

                        fromdate = epoch-2*Day;

                        enddate = epoch-Day;

                        break;

                    case "week":

                        fromdate = epoch-Week;

                        enddate = epoch;

                        break;

                }

            } else {

                output.push({ topic: "SELECT * FROM jumper_74 WHERE wellname='"+parts[0]+"' AND ID='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });

            }

        }

    }

}

//msg.topic = "SELECT * FROM batang_81 WHERE epoch >= " + fromdate + " AND epoch <= " + epoch ;

msg.topic = "SELECT left(datetime, 16) as mtimestamp, round(avg(temperature),2) as temperature, epoch, wellname, round(avg(pressure),2) as pressure, round(avg(flow),2) as flow FROM batang_81 WHERE epoch >= '" + fromdate + "' AND epoch <= '" + epoch + "' GROUP BY mtimestamp";

return msg;

Gladly if someone can help me, thanks.

[Colin Law]

Start by putting debug nodes on the datepicker outputs so you can see the format of the data, then you can work out what to do with it.

Colin

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/6fcfa067-d915-4240-9fe9-d86d1d90c439%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dan Bicks]

Irvan,

This is a cool implementation in to node red, please share the solution and flow once this has been worked out.

What SQL database are you using and how is this connected to node red?

Big thanks

Dans