NEWS
Vorschlag: Sammlung von SQL Statements (als Funktionen)
-
Ich finde die idee sehr interessant und auch Kandidat als Adapter aus zu bauen die Statistiken.
Ich stehe mir dann vier das man dem Adapter definiert weicher Date so zusammengefasst werden sollen.
Dieser werden dann aus der DB gelesen und wie in deinen bespiel als object festgelegt.
Das wäre eine schöne erreicherung zB zum Messen des energy Verbrauchs, Gewinnung oder so einig andere Beispiele
Zum Anfang ist allein die Möglichkeit dass mit JS machen zu können bereits genial.
Ich hatte vor einigen Monaten Mal eine vergleichbare Frage gestellt (ohne Lösung) zur Statistik Berechnung einer Wärme Gewinnung Installation
Nice!
–-----------------------
Send from mobile device
-
Es gibt auch immer noch die Idee eines "Sattistik/Aggregator"-Adapters der einfache Dinge wie Summen, Differenzen u.ä. machen könnte …
mal schauen ob/wann ich mal zu sowas komme
-
So ein erstes Skript mit „sql“ Funktionen zur Erstellung von Statistiken.
Der Code ist sicher etwas holprig (bin JavaScript Anfänger - funktioniert aber für mich).
Verbesserungsvorschläge sind herzlich willkommen !
Das Skript zieht aus der DB folgende Statistiken:
12_Monate:
Monatlicher Verbrauch der letzten 12 Monate (Json)
7_Tage
Täglicher Verbrauch der letzten 7 Tage (Json)
seitJahresbeginn/Monatsbeginn/Wochenbeginn/Tagesbeginn
Verbrauch seit Jahresbeginn (einzelner String)
Die Counter für die man die Statistiken haben möchte werden oben einfach in das Array eingetragen.
Im zweiten Array stehen sprechende Namen damit die Variablen auch vernünftig benannt werden.
// // Berechnet counter differenzen // var logging=true; // instanz und pfad var instanz="javascript." + instance + ".Statistiken."; // wrapper Objekt für aktuellen Zaehlerstand // sind alle in kWh var wrapperobj='wrapper.1.Counter.'; var counter=[]; counter[0]='pv-eigenverbrauch'; counter[1]='pv-gesamtproduktion'; counter[2]='hausnaschluss-abgabe'; counter[3]='wallbox'; counter[4]='hausnaschluss-bezug'; counter[5]='waschmaschine'; counter[6]='trockner'; var label=[]; label[0]='PV Eigenverbr.'; label[1]='PV Produktion'; label[2]='PV Einspeisung'; label[3]='Wallbox'; label[4]='Bezug aus Netz'; label[5]='Waschmaschine'; label[6]='Trockner'; // // 12 Monats Statistik // var statistik12monate="12_Monate."; var namebez="12 Monate."; counter.forEach(function(element) { createState(instanz+statistik12monate+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); // // 7 Tage Statistik // var statistik7tage="7_Tage."; var namebez="7 Tage"; counter.forEach(function(element) { log("7 Tage->"+element); createState(instanz+statistik7tage+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); // seit Tagesanfang var statistik_anfangTag="seitTagesbeginn."; var namebez="seit Tagesbeginn"; counter.forEach(function(element) { createState(instanz+statistik_anfangTag+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); var statistik_anfangWoche="seitWochenbeginn."; var namebez="seit Wochenbeginn"; counter.forEach(function(element) { createState(instanz+statistik_anfangWoche+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); var statistik_anfangMonat="seitMonatsbeginn."; var namebez="seit Monatsbeginn"; counter.forEach(function(element) { createState(instanz+statistik_anfangMonat+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); var statistik_anfangJahr="seitJahresbeginn."; var namebez="seit Jahresbeginn"; counter.forEach(function(element) { createState(instanz+statistik_anfangJahr+element, { name: namebez+' '+element, type: 'string', unit: 'kWh', }); }); schedule("36 0 * * *", function () { counter.forEach(function(element) { var id=wrapperobj+element; // 12 Monate var id_ergebnis=instanz+statistik12monate+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_counter_12_monate(id,id_ergebnis); // 7 Tage id_ergebnis=instanz+statistik7tage+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_counter_7_tage(id,id_ergebnis); }); }); schedule("*/15 0 * * *", function () { counter.forEach(function(element) { var id=wrapperobj+element; // seit Jahresanfang var jahresanfang=datum_jahresanfang(new Date()); id_ergebnis=instanz+statistik_anfangJahr+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_differenz_seit_datum(id,id_ergebnis,jahresanfang); // seit Monatsanfang var monatsanfang=datum_monatsanfang(new Date()); id_ergebnis=instanz+statistik_anfangMonat+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_differenz_seit_datum(id,id_ergebnis,monatsanfang); // seit Wochenanfang var wochenanfang=datum_wochenanfang(new Date()); id_ergebnis=instanz+statistik_anfangWoche+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_differenz_seit_datum(id,id_ergebnis,wochenanfang); // seit Tagesanfang var tagesanfang=datum_tagesanfang(new Date()); id_ergebnis=instanz+statistik_anfangTag+element; tolog(logging,id+" -> "+id_ergebnis); statistiken_differenz_seit_datum(id,id_ergebnis,tagesanfang); }); }); function statistiken_differenz_seit_datum (id,id_ergebnis,datum) { var ergebnis; var myQuery="SELECT round((max(val)-min(val)),0) as delta \ FROM iobroker.ts_number WHERE id=(select id from iobroker.datapoints where name='"+id+"') \ AND (FROM_UNIXTIME(substring(ts,1,10)) >= '"+datum+"');"; //log(myQuery); sendTo('sql.0', 'query', myQuery, function (result) { if (result.error) { //log(result.error); } else { ergebnis=String(result.result[0].delta); tolog(logging,id_ergebnis+" -> "+ergebnis); setState(id_ergebnis,ergebnis,true); } }); } function statistiken_counter_7_tage (id,id_ergebnis) { var myQuery="SELECT CONCAT(MONTH(FROM_UNIXTIME(substring(ts,1,10))),'-', DAY(FROM_UNIXTIME(substring(ts,1,10)))) AS Tag, round(max(val)-min(val),0) AS Verbrauch \ FROM iobroker.ts_number WHERE id=(select id from iobroker.datapoints where name='"+id+"') \ AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 7 day) \ AND CURRENT_DATE() \ GROUP BY MONTH(FROM_UNIXTIME(substring(ts,1,10))),DAY(FROM_UNIXTIME(substring(ts,1,10))) \ ORDER BY MONTH(FROM_UNIXTIME(substring(ts,1,10))) , DAY(FROM_UNIXTIME(substring(ts,1,10))) ;"; sendTo('sql.0', 'query', myQuery, function (result) { if (result.error) { //log(result.error); } else { // show result setState(id_ergebnis,JSON.stringify(result.result),true); } }); } function statistiken_counter_12_monate (id,id_ergebnis) { var myQuery="SELECT CONCAT(YEAR(FROM_UNIXTIME(substring(ts,1,10))),'-', MONTH(FROM_UNIXTIME(substring(ts,1,10)))) AS Monat, round(max(val)-min(val),0) AS Verbrauch \ FROM iobroker.ts_number WHERE id=(select id from iobroker.datapoints where name='"+id+"') \ AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 12 month) \ AND CURRENT_DATE() \ GROUP BY YEAR(FROM_UNIXTIME(substring(ts,1,10))),MONTH(FROM_UNIXTIME(substring(ts,1,10))) \ ORDER BY YEAR(FROM_UNIXTIME(substring(ts,1,10))) , MONTH(FROM_UNIXTIME(substring(ts,1,10))) ;"; sendTo('sql.0', 'query', myQuery, function (result) { if (result.error) { //log(result.error); } else { // show result setState(id_ergebnis,JSON.stringify(result.result),true); } }); } // allgemeines function datum_jahresanfang (date) { return (date).getFullYear()+"-01-01"; } function datum_monatsanfang (date) { var monat=1+date.getMonth(); return (date).getFullYear()+"-"+monat+"-01"; } function datum_tagesanfang (date) { var jahr=(date).getFullYear(); var monat=1+date.getMonth(); var tag = date.getDate(); return jahr+"-"+monat+"-"+tag; } function datum_wochenanfang(date){ var iDayOfWeek = date.getDay(); var iDifference = date.getDate() - iDayOfWeek + (iDayOfWeek === 0 ? -6:1); var woa=new Date(date.setDate(iDifference)); return woa.toISOString().slice(0,10); } function datum_gestern(datum){ var yesterday = new Date(datum.valueOf() - 1000*60*60*24); return yesterday.toISOString().slice(0,10); } function kalenderwoche (d){ d = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate())); d.setUTCDate(d.getUTCDate() + 4 - (d.getUTCDay()||7)); var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1)); var weekNo = Math.ceil(( ( (d - yearStart) / 86400000) + 1)/7); return weekNo; } function tolog(logging,val) { if (logging===true) { log(val); } }
-
Verbesserungsvorschläge sind herzlich willkommen ! `
Wenn dir der Index bei den Arrays egal ist, kannst du die auch im Block erzeugen:var counter = [ 'pv-eigenverbrauch', 'pv-gesamtproduktion', 'hausnaschluss-abgabe', 'wallbox', 'hausnaschluss-bezug', 'waschmaschine', 'trockner' ];
Elemente am Ende einfügen (ohne den Index zu kennen) geht per
counter.push('whatever');
Die Instanz würde ich dynamisch berechnen, eventuell führt jemand das Skript in Instanz 1, 2 oder höher aus:
var instanz="javascript." + instance + ".Statistiken.";
-
jo, danke.
Index bei den Arrays ist allerdings wg. der Benennung nicht egal.
-
Index bei den Arrays ist allerdings wg. der Benennung nicht egal. `
Ok. Nur falls das nicht klar wurde: mit meinem Vorschlag erhältst du die gleichen Indizes (wird automatisch ab 0 durchnummeriert), sie stehen halt nicht explizit im Quelltext.Wenn du aus irgendeinem Grund öfters von Hand nachschauen musst, dann bietet sich deine ausführlichere Variante an.
-
Danke danke - ist klar geworden. :lol:
-
Hallo Ihr,
Danke für das tolle Script, leider verwende ich einen SQL Express, der die Umwandlung mittels
FROM_UNIXTIME nicht unterstützt!
script.js.Energie.Verbrauchsstatistik: RequestError: 'FROM_UNIXTIME' is not a recognized built-in function name.
Gibt es Alternativen dazu den Zeitstempel umzuwandlen?
Gruss
Thomas
-
Wegen SQL Server Express und UNIXTIMESTAMP:
Da man sowieso dieses Konvertierung immer wieder braucht, ist mein Vorschlag, einfach dem SQL Server diese Funktion beizubringen. Dazu das SQL Server Managementstudio (ist ebenfalls kostenlos) öffen und zur iobroker-Datenbank oben links im Pulldownmenü wechseln. Dann den folgenden Text in das Abfragefenster einfügen und anschließend auf "Ausführen" klicken:
Der Funktionscode ist:
CREATE FUNCTION [dbo].[fn_ConvertToDateTime] (@Datetime BIGINT) RETURNS DATETIME AS BEGIN DECLARE @LocalTimeOffset BIGINT ,@AdjustedLocalDatetime BIGINT; SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE()) SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset RETURN (dateadd(ms, @AdjustedLocalDatetime%(3600*24*1000), dateadd(day, @AdjustedLocalDatetime/(3600*24*1000), '1970-01-01 00:00:00.0'))) END
Das Ergebnis ist ein Datum mit dem Datentyp datetime. Der ist auf 10ms genau. Man sieht das daran, das die letzte Stelle entwerder 0, 3 oder 7 ist. Das reicht aber in der Regel für uns. Die Funktion nimmt alledings keine Rücksich auf Sommer-Winterzeit.
Testabfrage:
Select top 10 *,dbo.fn_ConvertToDateTime(ts) as Datum from ts_number
Das ergibt in der Ausgabe eine neue Spalte mit dem berechneten Datum aus den vorhandenen Timestamp-Werten.
Damit wird aus der ersten Abfrage
var myQuery="SELECT round((max(val)-min(val)),0) as delta \ FROM ts_number WHERE id=(select id from datapoints where name='"+id+"') \ AND (dbo.fn_ConvertToDateTime(ts) >= '"+datum+"');";
und aus der zweitern Abfrage:
var myQuery="SELECT CONCAT(MONTH(dbo.fn_ConvertToDateTime(ts)),'-', DAY(dbo.fn_ConvertToDateTime(ts))) AS Tag, round(max(val)-min(val),0) AS Verbrauch \ FROM ts_number WHERE id=(select id from datapoints where name='"+id+"') \ AND dbo.fn_ConvertToDateTime(ts) BETWEEN dateadd(day,-7,GETDATE()) AND GETDATE() \ GROUP BY MONTH(dbo.fn_ConvertToDateTime(ts)),DAY(dbo.fn_ConvertToDateTime(ts)) \ ORDER BY MONTH(dbo.fn_ConvertToDateTime(ts)) , DAY(dbo.fn_ConvertToDateTime(ts)) ;";
Im Quelltext die Anweisungen so anpassen.
PS: Habe ich ohne Ausprobieren gemacht, sollte aber passen.
-
Vielen Dank!!
Perfekt - genau das was ich gesucht habe. Die Funktion konnte ich einspielen und Sie geht.
Mal sehen ob die Abfragen damit klappen.
Danke
Thomas
-
Ich möchte auch ein bisschen SQL-Statistik machen und die Skripte oben haben wir schon viel geholfen.
Jetzt eine weiterführende Frage:
Wie kann ich per SQL ermitteln, wie lange (pro Tag) ein boolean 'true' war?
Also wie lange z.B. ein Fenster offen war?
Vielen Dank!
piForscher
-
Ehm, wie läuft ne dein Query ohne Wrapper? Kann ich auch nur den DAtenpunktpfad angeben oder muss ich die ID vom SQL Datenpunkt angeben.
Oder ist es einfacher den Wrapper zu installieren, nur den hab ich bisher noch nicht gefunden.
Gruß und Danke
Update:
Ich hab es soweit hinbekommen, dass ich Daten vom SQL bekomme. ich musste auf Mysql noch was in der Config ändern, damit die Abfrage lief.
wurde das Script eventuel noch erweitert? Von meinen Wärmezähler kommen leider nur Wh, kein kWh. Bei den Geräten müsste man noch einen Parameter übergeben, ob noch mal ein Teiler genommen wird oder nicht.
Gruß