Jahresrückblick 2025 – unser neuer Blogbeitrag ist online! ✨

[reda]

Hallo zusammen und danke für die Vorarbeiten.
Ich hatte am Ende doch noch ein paar Schwierigkeiten das ganze zusammenzubauen, daher hier nochmal meine Komplettlösung, evtl. hilft das auch nochmal jemandem weiter.

//------------------------------------ generische SQL Abfrage ------------------------------------------------
function getFromSQL(id, start, end) {

    var myQuery = "select m.*, maxt.val as mxval, maxt.ts as mxts, mint.val as mival, mint.ts as mits \
                from \
                (SELECT ROUND(sum(val),2) AS sqlSU, ROUND(avg(val),2) AS sqlAV,   \
                COUNT(*) AS sqlAZ, min(val) AS sqlMIN, max(val) AS sqlMAX, \
                max(ts) AS sqlLast,     \
                min(ts)  AS sqlFirst    \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+ id + "') \
                AND  ts >= '"+ start + "' \
                AND  ts <= '"+ end + "') m, \
                \
                (SELECT val, ts \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+ id + "') \
                AND  ts >= '"+ start + "' \
                AND  ts <= '"+ end + "' order by val desc limit 0,1) maxt,  \
                \
                (SELECT val, ts \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+ id + "') \
                AND  ts >= '"+ start + "' \
                AND  ts <= '"+ end + "' order by val asc limit 0,1) mint";

    return new Promise((resolve, reject) => {
        sendTo('sql.0', 'query', myQuery, result => {
            if (result.error) { reject(result.error); }
            else { resolve(result.result) }
        });
    });
}

async function GetTestValues(){
var date_end = Date.now();
var date_start = date_end - 1000 * 3600 * 24; // gestern
var result = await getFromSQL('sql_name_evtl_auch_alias', date_start, date_end);
console.log(result[0]);
};