Monatlicher Stromverbrauch in Grafana visualisieren

[Jewe]

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:

Code Auswählen Erweitern

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:

Code Auswählen Erweitern

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

[ergerd]

Hallo Jens,

ich gehe davon aus das der Filter $__timeFilter(TIMESTAMP) bereits auf eine Monatsansicht filtert.

Dann könnte es vielleicht so funktionieren:

Code Auswählen Erweitern

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

[Jewe]

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

[ergerd]

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

[ch.eick]

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.

Code Auswählen Erweitern

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;


Code Auswählen Erweitern

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.
Du darfst diesen Dateianhang nicht ansehen.

VG  Christian

[Jewe]

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

[ch.eick]

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

Hallo Jens,
das wäre dann ja einfach nach dem gleichen Prinzip, nur mit den anderen readings

VG  Christian

[Jewe]

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.:

Code Auswählen Erweitern

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;

[ch.eick]

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

[Jewe]

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.