SQL Abfrage universal

[hanss]

Problem Asynchron:

Mit der oben vorgestellten Funktion möchte ich nun gerne verschiedene Berechnungen durchführen,
scheitere aber immer an dem synchronen Ablauf, bzw. mit callback Funktionen wird das Programm
sehr unübersichtlich.
Wie kann man das übersichtlicher machen:

//-------------------Hauptprogramm:
let SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.Regenmesser.menge'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_Niederschlag);

SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.SprinklerMenge'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_SprinklerIst);  

SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.RegenMorgen'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_RegenM;   

// Hier soll erst weitergerechnet werden,
// wenn alle SQL Abfragen fertig sind

var x = Niederschlag-SprinklerIst+RegenM
log("Differenz: "+x)
//-------------------Hauptprogramm Ende



function askSQL_Niederschlag(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    Niederschlag = sum/1000
}

function askSQL_SprinklerIst(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    SprinklerIst = sum*1000     // in Ltr.
}
function askSQL_RegenM(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    RegenM = sum    
}

[J.A.R.V.I.S.]

@hanss das Stichwort lautet async/await und Promise.

function askSQLgh(id, start, end) {

    const startTime = millis()

    return new Promise(resolve => {
		sendTo(
			'sql.0',
			'getHistory',
			{
				id: id,
				options:
				{
					start: start,
					end: end,
					aggregate: 'none'
				}
			},
			data => {

				let min = data.result[0].val;
				const min_ts = data.result[0].ts
				let max = min;
				const max_ts = data.result[0].ts
				let sum = 0;
				let mean = 0;
				const anzahl = data.result.length  // ACHTUNG: liefert nur 500 Ergebnisse
				let first = 0  // first value
				let last = 0   // last value

				for (let i = 0; i < anzahl; i++) {
					if (i==0){
						first = data.result[i].ts
					}

					if (i==(data.result.length-1)){
						last = data.result[i].ts
					}

					sum += data.result[i].val;

					if(data.result[i].val < min) {
						min = data.result[i].val; min_ts = data.result[i].ts;
					}

					if(data.result[i].val > max) {
						max = data.result[i].val; max_ts = data.result[i].ts;
					}
				}

				mean = sum / anzahl

				resolve(
					{
						min,
						min_ts,
						max,
						max_ts,
						mean,
						sum,
						anzahl,
						first,
						last,
						startTime
					}
				);
			}
		);
	});
}

und der Aufruf geschieht dann wie folgt:

const sqlResult = await askSQLgh('deineID', start, end);

Der Aufruf muss aber in einer Async Funktion stehen.

async function test() {
	const sqlResult = await askSQLgh('deineID', start, end);

       //hier kannst du dann mit dem zurückgegebenen Objekt weiterarbeiten.
}

[ostseeskipper]

@hanss
Hallo Zusammen,

ich versuche gerade das Script zum Laufen zu überreden.
allerdings kann es im Hauptprogramm mit DatstrSQL nichts anfangen.
vonDatum = new Date(DatstrSQL(vonDatum) + ' 00:00:00' )

muss irgendwie noch ein NPM Modul in den JS Adapter geladen werden?

var DatumHeuteUnix =  Date.now()                          // = new Date().valueOf()
var vonDatum = new Date(DatumHeuteUnix - 1000*3600*24)    // gestern
    vonDatum = new Date(DatstrSQL(vonDatum) + ' 00:00:00' )
 //   vonDatum = new Date('2020-07-21' + ' 00:00:00' )
var vonDatumUnix = vonDatum.getTime() 
var bisDatum =  new Date(vonDatumUnix)
    bisDatum =  new Date(DatstrSQL(bisDatum) + ' 23:59:59' )          // oder '2020-07-22 00:00:00'

[hanss]

@ostseeskipper

// liefert Datum im mySQL Format: 'JJJJ-MM-TT'
function DatstrSQL(utx){ 
    let ut = new Date(utx)
    let jahr = ut.getFullYear();
    let monat = str2(ut.getMonth()+1);
    let tag = str2(ut.getDate());
    return jahr + '-' + monat + '-' + tag;
}

[ostseeskipper]

@hanss
Hallo Hanss,

nun meckert es wegen "str2"
Ist das noch eine weitere eigene Funktion ?
DatTimestr wird aktuell auch noch als Hinweis bei mir im Editor unterstrichen.

Hast du dir zufällig ne eigene Funktionsbibliothek zusammengestellt?
Kenn das so von SQL Servern und Office AddIns. Ich beschäftige mich erst seit Feb mit JS und ioBroker daher bin ich mit den Bibliotheken noch nicht wirklich vertraut.
Gibt es die Möglichkeit, Funktionen die man in mehreren Scripten benötigt, auszulagern oder sich seine eigene Funktionsbibliothek zu schaffen?

[hanss]

@ostseeskipper

// new Date(unixtime in ms.) in "TT.MM.JJJJ SS:MM:SS"
function DatTimestr(utx){
    return Datstr(utx) + ' '+ Uhrstr(utx)
}

function str2(x){
   if(x.toString().length == 1) x = '0'+x
return x
}

https://forum.iobroker.net/topic/3248/funktion-scriptübergreifend-aufrufen

[ostseeskipper]

@hanss
Hallo hanss.

Danke für den Link, das sollte helfen

In der Funktion DatTimestr sind nun wiederum noch Datstr und Uhrstr als eigene Funktionen drin.
Magst Du die bitte auch noch schicken.

millis für die Ausführungszeit hab ich mir schon einfach mit

    var StartTime = new Date().getTime();

geholt.

Noch eine Frage da ich einfach nur neugierig bin.
Dauert der Programmlauf durch die Abarbeitung der eigenen Funktion millis nicht länger ?

[hanss]

@ostseeskipper sagte in SQL Abfrage universal:

Datstr

// new Date(unixtime in ms.) in "TT.MM.JJJJ"
function Datstr(utx){
    let ut = new Date(utx)
    let jahr = ut.getFullYear();
    let monat = str2(ut.getMonth()+1);
    let tag = str2(ut.getDate());
    return tag+"."+monat+"."+jahr
}

// new Date(unixtime in ms.) in "SS:MM:SS"
function Uhrstr(utx){
    let ut = new Date(utx)  
    let std = str2(ut.getHours());
    let min = str2(ut.getMinutes());
    let sec = str2(ut.getSeconds());
    return std+":"+min+":"+sec
}

// gibt millisec. zurück wie im Arduino
function millis(){
    let EndTime = new Date()
    return EndTime.getMilliseconds()
}

[ostseeskipper]

@hanss
Super nun ist alles zusammen und läuft.
Hab ein paar meiner Datenpunkte ausprobiert und ich bekomme die Werte. Braucht 8-9 millisekunden um aus ca 700 Werten das Min, Max, Mittelwert, Summe, Anzahl, Wann erster und wann letzer Wert zu ermitteln.
Besten Dank

[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]);
};