Zeitstemlel von SQL- Historyeintrag manipulieren

[Friedel]

@bananajoe
Der Eintrag wird automatisch geschrieben. das möchte ich auch gerne so lassen.
Bei einer bestimmten Bedingung (Trigger) soll der Zeitstempel geändert werden.
Das braucht auch nur für den letzten Datensatz gemacht werden.

[BananaJoe]

@friedel dann mit dem richtige Zeitstempel noch mal neu schreiben und den falschen löschen.
Liegt der "richtige" davor oder dahinter?

[OliverIO]

@friedel
Es dürfte zwar egal sein, aber wenn dann würde ich mit einem Update sql Befehl den Datensatz aktualisieren.
Wenn du nur den letzten aktualisieren willst, könntest du aber auch einen neuen zusätzlichen Datensatz schreiben.

Bei Datenbanken gibt es nicht wirklich ein davor und danach, da die Methode wie eine Datenbank dann wirklich physisch speichert für einen Nutzer ziemlich uninteressant ist.
Die Reihenfolge kommt über eine Sortierung, meist dann nach dem primary key, welcher meist die id ist.

[Friedel]

@bananajoe
Dein Vorschlag mit neu schreiben funktioniert ja.
Hat aber den Nachteil das ich den Wert nicht abfragen kann sondern nur für z.B. eChart nutzen kann.
Ich möchte nur den Zeitstempel des zuletzt eingetragenen Wertes ändern.

[OliverIO]

@friedel

Kein Problem. Dann einfach ein updatestatement mit einer where clause auf den datenpunkt id und dann einfach die max funktion auf die id oder den timestamp mit Limit 1 ausführen. Schon hast du den letzten Datensatz zu deinem datenpunkt.

[Friedel]

@oliverio
Mit Update habe ich es probiert, aber ich bin ganz ehrlich ich tue mich mit Javascript etwas schwer.
Würde es gerne in ein Blockly Javascriptfunktion mit Parameter packen und dann über Blockly ausführen.

[OliverIO]

@friedel

Wieso JavaScript? Ich würde das direkt mit sql machen. ok, ein wenig javascript ist auch dabei. mit blockly kenne ich mich leider nicht so gut aus

ergänzung:

hier das sql für den abruf des letzten eintrags anhand des datenpunktnamens.

SELECT tn.* FROM ts_number tn JOIN datapoints dp ON dp.id = tn.id WHERE dp.name = 'datenpunkt id' ORDER BY tn.ts DESC LIMIT 1

und hier der befehl zum aktualisieren

UPDATE ts_number
SET ts= 1742833899865
WHERE id = (
    SELECT dp.id
    FROM datapoints dp
    WHERE dp.name = 'datenpunkt id'
)
AND ts = (
    SELECT MAX(tn.ts)
    FROM ts_number tn
    JOIN datapoints dp ON dp.id = tn.id
    WHERE dp.name = 'datenpunkt id'
);

ggfs muss bei den FROM noch jeweils der Datenbankname davor geschrieben werden.
Also anhand eines Beispiels:

FROM ts_number

nach

FROM iobroker.ts_number

falls die Datenbank iobroker heißt

[Friedel]

@oliverio
Dieses ist mein Versuch. Aber er meldet direkt Fehler.

UPDATE daten_iobroker.ts_number SET ts = '1742832000000' WHERE id = 0_userdata.0.Test_SQL.PV_Ertrag_Stunde ORDER BY ts DESC LIMIT 1;

[OliverIO]

@friedel

das problem ist, das der datenpunktname nicht direkt in der tabelle drin steht, sondern auf 2 tabellen aufgeteilt ist. eine tabelle mit den datenpunktnamen und einer id und dann je nach datentyp eine tabelle mit den werten, die aber nur auf die id der datenpunkt tabelle referenziert.
daher muss man immer noch einen join machen.

ich kann zwar sql, hab mir aber jetzt nur schnell die mal mit chat gpt erstellen lassen. man muss chatgpt halt die informationen mitliefern wie bspw:

ich habe eine tabelle mit datapoints und einer spalte id und name, sowie eine tabelle 
ts_number mit den spalten id, welche der id in tabelle datapoints entspricht und einer spalte ts 
für einen timestamp, sowie eine spalte value. ich benötige nun ein sql zur abfrage zeitlich 
letzten wertes anhand eines datenpunkt namens und ein sql zur aktualisierung des werts

als ergebnis hat mit chatgpt sogar eine erklärung geliefert.
das kannst du auch zur fehlersuche verwenden.

schau dir auch mal den inhalt der datenbank an, die iobroker verwendet.
am besten für mysql/mariadb verwendest du myphpadmin. ist mit docker auch relativ schnell installiert

ich habe oben das update sql noch angepasst, da ich gemerkt habe das du ja nicht value, sonder ts anpassen willst

[OliverIO]

@friedel sagte in Zeitstemlel von SQL- Historyeintrag manipulieren:

Aber er meldet direkt Fehler.

für bessere hilfe ist es immer gut, die fehler auch gleich mit zu benennen.
"er meldet fehler" ist sehr generisch

gut hier wusste ich es, aber für die weitere verfolgung bitte so

[Friedel]

@oliverio
Danke für die Unterstützung.
Ich werde es gleich mal ausprobieren.
Mit chat gpt habe ich noch nicht gearbeitet, schau ich mir auch gleich mal an.

[OliverIO]

@friedel
gerade für anfänger ist das ideal.
auch zum erklären, einfach reinkopieren zeile leer lassen und nach erklärung fragen.
alles was mit strikter syntax versehen ist können LLMs sehr gut.
Man muss aber alles nochmal checken, aber du kannst mit chatgpt sprechen wie mit einem hilfsprogrammierer. wenn du dann sagst das du anfänger bist, erklärt chatgpt auch mehr,detaillierter und einfacher, so das es anfänger auch verstehen. ansonsten immer nachfragen

[Friedel]

@oliverio

Ich komme hier einfach nicht weiter.
Den letzten Eintrag anzeigen zu lassen klappt schon nicht.

Wahrscheinlich ist es nur eine Kleinigkeit, aber ich habe mich hier festgefahren.
Vieleicht kann mir noch einer auf die Sprünge helfen.

javascript.0	20:18:57.377	info	script.js.common.SQL_Datenbank.Zeitstempel_1: sendTo(instance=sql.0, cmd=query, msg="SELECT * FROM daten_iobroker.ts_number WHERE id = '0_userdata.0.Test_SQL.PV_Ertrag_Stunde' ORDER BY ts DESC LIMIT 1;", hasCallback=true)
javascript.0	20:18:57.377	info	script.js.common.SQL_Datenbank.Zeitstempel_1: sendTo[custom] sql.0: SELECT * FROM daten_iobroker.ts_number WHERE id = '0_userdata.0.Test_SQL.PV_Ertrag_Stunde' ORDER BY ts DESC LIMIT 1;
javascript.0	20:18:57.378	info	script.js.common.SQL_Datenbank.Zeitstempel_1: registered 0 subscriptions, 0 schedules, 0 messages, 0 logs and 0 file subscriptions
javascript.0	20:18:57.386	info	script.js.common.SQL_Datenbank.Zeitstempel_1: { error: null, result: [] }

[OliverIO]

hatte ich ja oben schon geschrieben

@oliverio sagte in Zeitstemlel von SQL- Historyeintrag manipulieren:

das problem ist, das der datenpunktname nicht direkt in der tabelle drin steht, sondern auf 2 tabellen aufgeteilt ist. eine tabelle mit den datenpunktnamen und einer id und dann je nach datentyp eine tabelle mit den werten, die aber nur auf die id der datenpunkt tabelle referenziert.
daher muss man immer noch einen join machen.

in der tabelle ts_numer ist die spalte id eine nummer. das mapping von id zu datenpunktname erfolgt in der tabelle datapoints. daher muss man ein join machen.

du hast 2 möglichkeiten
a) auswahl über den datenpunktnamen wie folgt:

SELECT tn.* 
FROM ts_number tn 
JOIN datapoints dp 
ON dp.id = tn.id 
WHERE dp.name = 'datenpunkt id' 
ORDER BY tn.ts DESC 
LIMIT 1 

oder b)
du suchst in tabelle datapoint die id passend zu deinem datenpunkt heraus (wie gesagt das ist eine nummer)

dann kannst du auch so machen. 123 dann gegen die eigentliche id austauschen

SELECT * 
FROM daten_iobroker.ts_number 
WHERE id = 123 
ORDER BY ts DESC 
LIMIT 1

[Friedel]

@oliverio

Hallo, habe es jetzt hinbekommen so wie Du es geasgt hattest.

Hier einmal die Abfrage nach dem letzten Eintrag.

sendTo('sql.0', 'query', 'SELECT * FROM daten_iobroker.ts_number WHERE id = 33 ORDER BY ts DESC LIMIT 1', async (result) => {
    console.info(result);
});

Und hier einmal wo der Zeitstempel vom letzten Eintrag geändert wird.

sendTo('sql.0', 'query', 'UPDATE daten_iobroker.ts_number SET ts = \'1743082140098\' WHERE id = 33 ORDER BY ts DESC LIMIT 1');

Nun würde ich aber gerne die ID und den Zeitstempel als Variable übergeben. Dieses bekomme ich aber nicht hin.

Kann mir hier noch einer weiterhelfen?

[Friedel]

@friedel sagte in Zeitstemlel von SQL- Historyeintrag manipulieren:

@oliverio

Hallo, habe es jetzt hinbekommen so wie Du es geasgt hattest.

Hier einmal die Abfrage nach dem letzten Eintrag.

sendTo('sql.0', 'query', 'SELECT * FROM daten_iobroker.ts_number WHERE id = 33 ORDER BY ts DESC LIMIT 1', async (result) => {
    console.info(result);
});

Und hier einmal wo der Zeitstempel vom letzten Eintrag geändert wird.

sendTo('sql.0', 'query', 'UPDATE daten_iobroker.ts_number SET ts = \'1743082140098\' WHERE id = 33 ORDER BY ts DESC LIMIT 1');

Nun würde ich aber gerne die ID und den Zeitstempel als Variable übergeben. Dieses bekomme ich aber nicht hin.

Kann mir hier noch einer weiterhelfen?

[Friedel]

@friedel
Die Frage ist wie ich die Variabel übergeben muss?

var Datenpunkt = _id;
var Timestamp = _ts;

sendTo('sql.0', 'query', 'SELECT * FROM daten_iobroker.ts_number WHERE id == Datenpunkt ORDER BY ts DESC LIMIT 1', async (result) => {
    console.info(result);
});

console.log(Datenpunkt);
console.log(Timestamp);

[OliverIO]

@friedel

Der SQL Befehl wird als Text übergeben.
Wenn du da variablen einbauen willst, musst du einen Textring so zusammenbauen, dass die variablen dort eingefügt werden.
Am besten geht das mit einem so genannten Template String
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals

[Friedel]

@oliverio
Ich komme immer etwas weiter

var Datenpunkt = _id;
var Timestamp = _ts;

var sqlQuery_1 = "SELECT * FROM daten_iobroker.ts_number WHERE id = " + Datenpunkt;
var sqlQuery_2 = "ORDER BY ts DESC LIMIT 1";

console.log(sqlQuery_1);
console.log(sqlQuery_2);

sendTo('sql.0', 'query', sqlQuery_1, sqlQuery_2, async (result) => {
    console.info(result);
});

Aber noch eine Fehlermeldung obwohl der Befehl doch jetzt passen müsste.

javascript.0	16:49:37.930	info	Start JavaScript script.js.common.SQL_Datenbank.AAAA (Blockly)
javascript.0	16:49:37.957	info	script.js.common.SQL_Datenbank.AAAA: SELECT * FROM daten_iobroker.ts_number WHERE id = 33
javascript.0	16:49:37.958	info	script.js.common.SQL_Datenbank.AAAA: ORDER BY ts DESC LIMIT 1
javascript.0	16:49:37.959	error	script.js.common.SQL_Datenbank.AAAA: Error: Parameter "options" needs to be a real object but type "string" has been passed
javascript.0	16:49:37.960	error	at etwas_tun (script.js.common.SQL_Datenbank.AAAA:40:5)
javascript.0	16:49:37.960	error	at script.js.common.SQL_Datenbank.AAAA:64:7
javascript.0	16:49:37.960	error	at script.js.common.SQL_Datenbank.AAAA:78:3

[OliverIO]

@friedel

Die Parameter haben eine feste Reihenfolge
Du hast jetzt