Collecting SQLite Data and Push Them Specific Time

[wtrk]

Hi everyone.

Before I want to say, Im trying to make a project Raspberry PI 3 and Nodered.

I'm logging my data from device to Litesql everything is ok.

I just want to take this data from db and make a chart for a specific  time, like today, yesterday, last week, last month, last year.

 

I found a code, and need to change this code for my project. Can some one help me. My db name is Test  and column name is Value

Which line or lines I have to change and how ?

 

This code is working code, which is I take in internet , and I changed just db name and source.

// This will handle any device and any attribute as long as it is in the DB
var p_30d  = 1000*60*60*24*30 ; //30 Days
var p_7d  = 1000*60*60*24*7 ; //7 Days
var p_1d   =  1000*60*60*24 ; // 1 Day
var d = new Date();
var current = d.getTime();
var today0h = d.setHours(0,0,0,0);
var day = d.getDay();
var monday0h = today0h - (day + (day === 0 ? -6:1)) * p_1d;
var fromdate = 0;
var enddate = 0;
var sql = [];
var sourcelist = [];
var aggrlist = [];
var title = "";


// Get the period and the list of data sources 
// also set some default values if one or the other does not exist yet
sourcelist = context.get("sourcelist");
if (sourcelist===undefined) { // if running for the first time
    sourcelist = [];
}
aggrlist = context.get("aggrlist");
if (aggrlist===undefined) { // if running for the first time
    aggrlist = [];
}
fromdate = context.get("fromdate");
if (fromdate===undefined) {
    // set the period to a default if it is not selected yet
    fromdate = current-p_1d;
}
enddate = context.get("enddate");
if (enddate===undefined) {
    // set the period to a default if it is not selected yet
    enddate = current;
}

switch(msg.topic) {
    case "period":
        switch(msg.payload) {
            case "today":
                fromdate = today0h;
                enddate = today0h+p_1d;
                break;
            case "yesterday":
                fromdate = today0h-p_1d;
                enddate = today0h;
                break;
            case "thisweek":
                fromdate = monday0h;
                enddate = monday0h+p_7d;
                break;
            case "lastweek":
                fromdate = monday0h-p_7d;
                enddate = monday0h;
                break;
            case "last24h":
                fromdate = current-p_1d;
                enddate = current;
                break;
            case "last7d":
                fromdate = current-p_7d;
                enddate = current;
                break;
            case "last30d":
                fromdate = current-p_30d;
                enddate = current;
                break;
        }
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
    case "datasource":
        if (msg.payload==="delete") {
            // remove all previous data sources
            sourcelist = [];
        } else {
            sourcelist = context.get("sourcelist");
            if (sourcelist===undefined) { // if running for the first time
                sourcelist = [];
            }
            sourcelist.push(msg.payload);
        }
        context.set("sourcelist",sourcelist);
        break;
    case "aggrsource":
        if (msg.payload==="delete") {
            // remove all previous data sources
            aggrlist = [];
        } else {
            aggrlist = context.get("aggrlist");
            if (aggrlist===undefined) { // if running for the first time
                aggrlist = [];
            }
            aggrlist.push(msg.payload);
        }
        context.set("aggrlist",aggrlist);
        break;
    case "minus1w":
        fromdate = fromdate-p_7d;
        enddate = enddate-p_7d;
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
    case "plus1w":
        fromdate = fromdate+p_7d;
        enddate = enddate+p_7d;
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
    case "minus1d":
        fromdate = fromdate-p_1d;
        enddate = enddate-p_1d;
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
    case "plus1d":
        fromdate = fromdate+p_1d;
        enddate = enddate+p_1d;
        context.set("fromdate",fromdate);
        context.set("enddate",enddate);
        break;
}


// Regenerate the SQL statements
// Run through the data source list an generate the SQL statements
sql = [];
if (sourcelist.length>0) {
    for (var i = 0; i < sourcelist.length; i++) {
        var parts = sourcelist[i].split("/");
        sql.push({ topic: "SELECT * FROM sensor_data WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
    }
} 
if (aggrlist.length>0) {
    for (var i = 0; i < aggrlist.length; i++) {
        var parts = aggrlist[i].split("/");
        sql.push({ topic: "SELECT * FROM sensor_aggr WHERE device='"+parts[0]+"' AND sensor='"+parts[1]+"' AND epoch >= " + fromdate + " AND epoch <= " + enddate });
    }
} 
if (sql.length===0) {    
    // Dummy select that returns nothing to clear the chart
    sql.push({ topic: "SELECT * FROM sensor_data WHERE device='xxxx'" });
}

// set the completed flag for the join node later
sql[sql.length-1].complete=true;
// pass along the email flag to redirect the flow later
if (msg.topic==="email") {
    sql[sql.length-1].email=true;
}

// Generate report title
if (sourcelist.length===0 && aggrlist.length===0) {
    title = "No data source";
} else {
    if (sourcelist.length!==0) {
        title = title + sourcelist.toString()+ ", ";
    }
    if (aggrlist.length!==0) {
        title = title + aggrlist.toString()+ ", ";
    }
    title = title.substring(0,title.length-2);
    title = title + " | ";

    var d = new Date();
    d.setTime(fromdate);
    var yyyy = d.getFullYear();
    var mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
    var dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
    var hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
    var mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
    var ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
    title = title + dd + "." + mm + "." + yyyy;
    d.setTime(enddate);
    yyyy = d.getFullYear();
    mm = d.getMonth() < 9 ? "0" + (d.getMonth() + 1) : (d.getMonth() + 1); // getMonth() is zero-based
    dd  = d.getDate() < 10 ? "0" + d.getDate() : d.getDate();
    hh = d.getHours() < 10 ? "0" + d.getHours() : d.getHours();
    mmm  = d.getMinutes() < 10 ? "0" + d.getMinutes() : d.getMinutes();
    ss  = d.getSeconds() < 10 ? "0" + d.getSeconds() : d.getSeconds();
    title = title + " - " + dd + "." + mm + "." + yyyy;
}
sql[sql.length-1].title=title;

return [ sql ];