Hallo,
versuche seit ein paar Tagen den Monatliche Stromverbrauch in Grafana als Balkendiagramm zu visualisieren. Dazu möchte ich vom aktuellen Wert den letzten wert vom vormonat abziehen. Damit habe ich dann den monatlichen verbrauch.
Mit folgendem Query bekomme ich die Monatssumme aufsteigend angezeigt:
SELECT
UNIX_TIMESTAMP(LAST_DAY(DATE_FORMAT(TIMESTAMP, '%Y-%m-01'))) AS time_sec,
CAST(VALUE AS SIGNED) AS Stromverbrauch
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = 'MQTT2_Stromzaehler' AND READING = 'SENSOR_Strom_Total_in'
Nun hab ich probiert:
SELECT
UNIX_TIMESTAMP(LAST_DAY(DATE_FORMAT(TIMESTAMP, '%Y-%m-01'))) AS time_sec,
CAST(VALUE AS SIGNED) -
COALESCE(LAG(CAST(VALUE AS SIGNED)) OVER (ORDER BY TIMESTAMP)) AS Stromverbrauch
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = 'MQTT2_Stromzaehler' AND READING = 'SENSOR_Strom_Total_in'
Damit bekomme ich aber leider nicht die richtigen werte sondern nur ??? wenig...
Kann mir jemand helfen, bin leider mit meinen sql künsten am ende :-)
Danke, Jens
Hallo Jens,
ich gehe davon aus das der Filter $__timeFilter(TIMESTAMP) bereits auf eine Monatsansicht filtert.
Dann könnte es vielleicht so funktionieren:
SELECT
UNIX_TIMESTAMP(LAST_DAY(DATE_FORMAT(TIMESTAMP, '%Y-%m-01'))) AS time_sec,
CAST(VALUE AS SIGNED) -
(select sum(t2.value) from history t2 where t2.DEVICE = 'MQTT2_Stromzaehler' AND t2.READING = 'SENSOR_Strom_Total_in'
and YEAR(t2.timestamp) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
and MONTH(t2.timestamp) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)) AS Stromverbrauch
FROM history
WHERE $__timeFilter(TIMESTAMP) AND DEVICE = 'MQTT2_Stromzaehler' AND READING = 'SENSOR_Strom_Total_in'
Grüße
Rainer
Hallo Rainer,
danke für Deinen Input, geht so leider nicht. da kommen riesige negative Werte raus und das System ist damit ein wenig überlastet.
In Verbindung mit einer InfluxDB gibt es die Funktion difference(). Das wäre genau das, was ich suche. Gibt es wowas nicht auch mit mySQL bzw. MariaDB?
Jens
Sorry, InfluxDB kenne ich nicht.
Bei den Tests des Sql von mir habe ich den aktuellen mit dem Vormonat verglichen,
der muss natürlich ein negatives Ergebnis bringen, der aktuelle Monat ist ja erst
halb rum.
Grüße
Rainer
Moin,
ich hätte da auch etwas gefunden.
Zum Testen wäre es hier direkt als MySQL. Bei Grafana sind dann die Kommentare zu ändern.
SELECT
UNIX_TIMESTAMP(LAST_DAY(DATE_FORMAT(TIMESTAMP, '%Y-%m-01'))) AS time_sec,
sum(delta) AS Stromverbrauch
FROM
( SELECT
TIMESTAMP,
VALUE,
@diff:= if(@prev=0,0,(VALUE - @prev)) AS delta,
@prev:= VALUE
FROM history,(SELECT @prev:=0)x
WHERE DEVICE = 'EVU_StromZaehler'
AND READING = 'Strom_Status-02'
AND TIMESTAMP > '2023-01-01' -- $__timeFilter(TIMESTAMP)
AND VALUE != 'nicht ' -- das muss auch weg
ORDER BY TIMESTAMP
)x
GROUP BY time_sec;
time_sec Stromverbrauch
1675123200 486
1677542400 245
1680220800 67
1682812800 4
1685491200 4
1688083200 4
1690761600 9
1693440000 4
1696032000 36
1698710400 169
1701302400 807
1703980800 1112
1706659200 1182
1709164800 485
EDIT: Ich habe es mal in mein Jahres Diagramm eingebaut und bis auf den Plot Abriss am Jahresanfang passt es.
Screenshot 2024-02-21 170747.png
VG Christian
Hey,
vielen Dank für den Input, das hat mich nun ein ganzen Stück weitergebracht. Als Graph sieht das schon ganz gut aus.
Beim Balkendiagramm passt der Stromverbrauch auch. Im Moment versuche ich noch in das Balkendiagramm auch noch die Einspeisung und die PV-Erzeugung einzubauen. Habe leider nur zu wenig Zeit ;-)
Jens
Zitat von: Jewe am 25 Februar 2024, 17:06:15Beim Balkendiagramm passt der Stromverbrauch auch. Im Moment versuche ich noch in das Balkendiagramm auch noch die Einspeisung und die PV-Erzeugung einzubauen. Habe leider nur zu wenig Zeit ;-)
Hallo Jens,
das wäre dann ja einfach nach dem gleichen Prinzip, nur mit den anderen readings ;-)
VG Christian
Hallo Christian,
ja klar, so habe ich es nun auch gemacht. Einzig bei den Balken bekomme ich auf der X-Achse monat und Jahr nicht angezeigt.
Der Code sieht so aus, eben 3 mal.:
SELECT
UNIX_TIMESTAMP(LAST_DAY(DATE_FORMAT(TIMESTAMP, '%Y-%m-01'))) AS time_sec,
sum(delta) AS Stromverbrauch
FROM
( SELECT
TIMESTAMP,
VALUE,
@diff:= if(@prev=0,0,(VALUE - @prev)) AS delta,
@prev:= VALUE
FROM history,(SELECT @prev:=0)x
WHERE DEVICE = 'MQTT2_Stromzaehler'
AND READING = 'SENSOR_Strom_Total_in'
AND $__timeFilter(TIMESTAMP)
ORDER BY TIMESTAMP
)x
GROUP BY time_sec;
Hallo Jens,
das habe ich bisher ja auch noch nicht gemacht, versuch mal mit beim Panel mit den Overrides und Axes, da gibt es verschiedene Modi für x-Axes. Oder einfach im Grafana Forum mal nachfragen, da gibt es doch sicherlich ein Muster für diese Art der Diagramme, wo Du dann die MySQL SELECT einfach einsetzen kannst.
VG Christian
So, nun ist es fertig. Mit einem Override Fields with name "time_sec" und Standard options>Unit "time: MM/YY" passt es nun.
Vielen Dank an die Helfer.