Photovoltaik Eigenverbrauch,Bilanz,Prognose (Kostal Plenticore; KSEM; BYD HV)

kaiman · 03 April 2022, 07:29:20

Hallo zusammen,

ich bräuchte noch mal eure Hilfe:

Gestern hatte ich einen längeren Stromausfall und FHEM hat neu gestartet, leider passen jetzt die Werte für den Monat April nicht mehr:
Statistik vom 2022-04-03 in kWh   aktuell   Heute   Monat   Jahr / Vorjahr
Bezug von PV   0308 W   0000   -606
Bezug ins Haus (Energieverbrauch)   0477 W   0003   -322

Kann ich irgendwo antriggern, dass die Werte neu erzeugt werden?
Die Tageswerte waren gestern auch komplett falsch, wurden heute nacht aber ja zurückgesetzt.
Die Jahreswerte passen noch.

ch.eick · 03 April 2022, 14:58:15

Zitat von: kaiman am 03 April 2022, 07:29:20
Hallo zusammen,

ich bräuchte noch mal eure Hilfe:

Gestern hatte ich einen längeren Stromausfall und FHEM hat neu gestartet, leider passen jetzt die Werte für den Monat April nicht mehr:
Statistik vom 2022-04-03 in kWh   aktuell   Heute   Monat   Jahr / Vorjahr
Bezug von PV   0308 W   0000   -606
Bezug ins Haus (Energieverbrauch)   0477 W   0003   -322

Kann ich irgendwo antriggern, dass die Werte neu erzeugt werden?
Die Tageswerte waren gestern auch komplett falsch, wurden heute nacht aber ja zurückgesetzt.
Die Jahreswerte passen noch.

Schau mal ob im WR_1_API die Initiative readings für Tag, Monat und Jahr richtig gesetzt sind. Eventuell ist jetzt noch der Wert vom 1.4 0:00 nicht richtig.
Nimm einfach den letzten vom 31.03, dann sollte es passen.

Gruß Christian

kaiman · 03 April 2022, 15:26:51

Hallo,

DANKE! Hat funktioniert. Hab die Daten vom 01.04. 01:00 in SW_Meter_init_FeedInGrid_Month und SW_Meter_init_Grid_Month eingetragen.

ch.eick · 03 April 2022, 16:35:42

Zitat von: kaiman am 03 April 2022, 15:26:51
Hallo,

DANKE! Hat funktioniert. Hab die Daten vom 01.04. 01:00 in SW_Meter_init_FeedInGrid_Month und SW_Meter_init_Grid_Month eingetragen.

Die anderen bereits geschriebenen Werte in der DB sollten dann um diesen Wert zu hoch sein. Die könntest Du dann ja noch korrigieren,
damit die Datenbank bei Graphen wieder stimmert.

majestro84 · 08 April 2022, 08:52:31

Hallo Christian,

Ich habe aktuell Probleme den Wert für das erste Quartal zubekommen. Da ich letztes Jahr Ende Dezember die Anlage erst in Betrieb genommen habe fällt mir das jetzt erst auf.
Die Abfrage LogDBRep_Statistic_previous_Quarter gibt leider keine Werte raus. Wenn ich mir die einzelnen Werte in WR_1_Api anschaue sind diese aber wohl vorhanden.
Hast du evtl. ein Tipp für mich wie ich das Problem eingrenzen kann?

List LogDBRep_Statistic_previous_Quarter

Code Auswählen

Internals:
   DATABASE   fhem
   DEF        logdb
   FUUID      61e156c6-f33f-3405-689b-6b14f28883227972
   FVERSION   93_DbRep.pm:v8.48.2-s25731/2022-02-22
   LASTCMD    sqlCmd SELECT * FROM ( SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  'previous' AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL @offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QA ) UA  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QB  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  IF( MONTH(h.TIMESTAMP) != 12 , cast(h.VALUE/1000 AS decimal(6)) , 0 ) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QC  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  IF( MONTH(h.TIMESTAMP) != 12 , cast(h.VALUE/1000 AS decimal(6)) , 0 ) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 13+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 12+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QD ;
   MODEL      Client
   NAME       LogDBRep_Statistic_previous_Quarter
   NOTIFYDEV  global,LogDBRep_Statistic_previous_Quarter
   NR         807
   NTFY_ORDER 50-LogDBRep_Statistic_previous_Quarter
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       1
   HELPER:
     DBLOGDEVICE logdb
     GRANTS     ALL PRIVILEGES,USAGE
     IDRETRIES  2
     MINTS      2019-07-12 13:30:00
     PACKAGE    main
     VERSION    8.48.2
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   OLDREADINGS:
   READINGS:
     2022-03-31 23:59:00   Q1              previous
     2021-06-30 23:59:00   Q2              
     2021-09-30 23:59:00   Q3              
     2021-12-31 23:59:00   Q4              
     2022-04-08 08:36:08   SqlResultRow_1  TIMESTAMP|READING|VALUE
     2022-04-08 08:36:08   SqlResultRow_2  2022-03-31 23:59:00|Q1|previous
     2022-04-08 08:36:08   SqlResultRow_3  2021-12-31 23:59:00|Q4|
     2022-04-08 08:36:08   SqlResultRow_4  2021-09-30 23:59:00|Q3|
     2022-04-08 08:36:08   SqlResultRow_5  2021-06-30 23:59:00|Q2|
     2022-04-08 08:36:08   sqlCmd          SELECT * FROM ( SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  'previous' AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL @offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QA ) UA  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QB  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  IF( MONTH(h.TIMESTAMP) != 12 , cast(h.VALUE/1000 AS decimal(6)) , 0 ) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QC  UNION ALL  SELECT  TIMESTAMP,  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,  VALUE FROM (  SELECT  DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,  '_Year' AS READING,  null AS VALUE  UNION ALL  SELECT * FROM  (  SELECT  DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,  end.READING,  (end.VALUE-begin.VALUE) AS VALUE  FROM  (  SELECT  h.TIMESTAMP,  h.READING,  cast(h.VALUE/1000 AS decimal(6)) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) end  INNER JOIN  (   SELECT  h.TIMESTAMP,  h.READING,  IF( MONTH(h.TIMESTAMP) != 12 , cast(h.VALUE/1000 AS decimal(6)) , 0 ) AS VALUE  FROM history h  INNER JOIN  (  SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history  WHERE DEVICE = 'WR_1_API'  AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )  AND READING NOT LIKE '%Autarky%'  AND READING NOT LIKE '%Rate%'  AND READING NOT LIKE '%NoBat%'  AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 13+@offset MONTH, '%Y/%m/28' )  AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 12+@offset MONTH, '%Y/%m/01' )  GROUP BY READING  ) x1  ON h.TIMESTAMP = x1.TIMESTAMP  AND h.READING = x1.READING  AND h.VALUE != 0  ) begin  ON end.READING = begin.READING  AND MONTH(end.TIMESTAMP) IN (3,6,9,12)  ) QX ) QD ;
     2022-04-08 08:36:08   sqlResultNumRows 4
     2022-04-08 08:36:08   state           done
Attributes:
   DbLogExclude .*
   allowDeletion 0
   comment    Version 2022.01.14 10:00
   device     WR_1_API
   reading    SW_Statistic%_Year,Statistic_EnergyHomeBat_Year EXCLUDE=%Autarky%,%Rate%,%NoBat%
   room       Statistik
   sqlCmdVars SET @offset:=  (   CASE WHEN MONTH(CURRENT_DATE) IN (1,4,7,10) THEN @offset:=0          WHEN MONTH(CURRENT_DATE) IN (2,5,8,11) THEN @offset:=1       ELSE @offset:=2   END  );
   userExitFn splitReading .*:.*
   verbose    0

List von WR_1_API als txt Datei.

Die splitReading Funtion sie wie folgt aus

Code Auswählen

sub splitReading {
 my ($name,$reading,$value) = @_;
 my $hash = $defs{$name};

 if($reading =~ /^.*SqlResultRow_.*$/ and
    $value   =~ /^(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)\|(.*)\|(.*)/ ) {

     my $TIMESTAMP = "$1-$2-$3 $4:$5:$6";
     my $READING   = "$7";
     my $VALUE     = "$8";

     setReadingsVal($hash,$READING,$VALUE,$TIMESTAMP);
 }
return;
}

Vielen Dank schon mal für deine Mühe.

VG Alex

ch.eick · 08 April 2022, 10:13:46

Zitat von: majestro84 am 08 April 2022, 08:52:31
Hallo Christian,

Ich habe aktuell Probleme den Wert für das erste Quartal zubekommen. Da ich letztes Jahr Ende Dezember die Anlage erst in Betrieb genommen habe fällt mir das jetzt erst auf.
Die Abfrage LogDBRep_Statistic_previous_Quarter gibt leider keine Werte raus. Wenn ich mir die einzelnen Werte in WR_1_Api anschaue sind diese aber wohl vorhanden.
Hast du evtl. ein Tipp für mich wie ich das Problem eingrenzen kann?

Hallo Alex,
das ist ja noch ganz neu und ich schaue es mir bereits an

Q2, Q3 und Q4 sind bei mir für 2021 noch okay.
Q1 liefert falsche Werte, wird aber als "previous" und mit dem richtigen Datum bereits erkannt.
Auch die Anzeige im WR_1_API reagiert bereits und zeigt die falschen Werte bereits an.

Nun schaue ich mir mal das SQL SELECT an ...

EDIT: Das Problem ist natürlich der Jahreswechsel

am 01.01. wird ja alles wieder auf Null gesetzt, was ich total verpennt habe.
Es dauert dann jetzt leider noch etwas, damit Q1 richtig berechnet wird.

VG
Christian

majestro84 · 08 April 2022, 16:02:49

Hallo Christian

Danke für deine schnelle Antwort und deine Bemühung.

VG Alex

ch.eick · 08 April 2022, 18:01:37

Zitat von: majestro84 am 08 April 2022, 16:02:49
Hallo Christian

Danke für deine schnelle Antwort und deine Bemühung.

VG Alex

Hallo Alex,
bitte teste das SQL SELECT dann nochmal mit Deinen Daten, es sollte jetzt so passen.

SqlCmd für das DbRep Device LogDBRep_Statistic_previous_Quarter

Code Auswählen


SELECT * FROM
(
SELECT
  TIMESTAMP,
  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,
  VALUE
FROM
(
  SELECT
    DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,
    '_Year'    AS READING,
	'previous' AS VALUE
  UNION ALL
  SELECT * FROM
    (
     SELECT
       DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,
       end.READING,
       (end.VALUE-begin.VALUE) AS VALUE
     FROM
     (
         SELECT
           h.TIMESTAMP,
           h.READING,
           cast(h.VALUE/1000 AS decimal(6)) AS VALUE
         FROM history h
       INNER JOIN
         (
         SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
          	 AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1+@offset MONTH, '%Y/%m/28' ) 
             AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL @offset MONTH, '%Y/%m/01' )
           GROUP BY READING
         ) x1
       ON    h.TIMESTAMP = x1.TIMESTAMP
         AND h.READING   = x1.READING
         AND h.VALUE != 0
     ) end
   INNER JOIN
     (	
       SELECT
         h.TIMESTAMP,
         h.READING,
         if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE
       FROM history h
     INNER JOIN
       (
        SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
        	AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' ) 
            AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )
          GROUP BY READING
       ) x1
     ON    h.TIMESTAMP = x1.TIMESTAMP
       AND h.READING   = x1.READING
       AND h.VALUE != 0
     ) begin
   ON    end.READING = begin.READING
     AND MONTH(end.TIMESTAMP) IN (3,6,9,12)
   ) QX
) QA
) UA

UNION ALL

SELECT
  TIMESTAMP,
  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,
  VALUE
FROM
(
  SELECT
    DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,
    '_Year' AS READING,
	null    AS VALUE
  UNION ALL
  SELECT * FROM
    (
     SELECT
       DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,
       end.READING,
       (end.VALUE-begin.VALUE) AS VALUE
     FROM
     (
         SELECT
           h.TIMESTAMP,
           h.READING,
           cast(h.VALUE/1000 AS decimal(6)) AS VALUE
         FROM history h
       INNER JOIN
         (
         SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
           WHERE §device§ AND §reading§
          	 AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' ) 
             AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )
           GROUP BY READING
         ) x1
       ON    h.TIMESTAMP = x1.TIMESTAMP
         AND h.READING   = x1.READING
         AND h.VALUE != 0
     ) end
   INNER JOIN
     (	
       SELECT
         h.TIMESTAMP,
         h.READING,
         if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE
       FROM history h
     INNER JOIN
       (
        SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
        	AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' ) 
            AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )
          GROUP BY READING
       ) x1
     ON    h.TIMESTAMP = x1.TIMESTAMP
       AND h.READING   = x1.READING
       AND h.VALUE != 0
     ) begin
   ON    end.READING = begin.READING
     AND MONTH(end.TIMESTAMP) IN (3,6,9,12)
   ) QX
) QB

UNION ALL

SELECT
  TIMESTAMP,
  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,
  VALUE
FROM
(
  SELECT
    DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,
    '_Year' AS READING,
	null    AS VALUE
  UNION ALL
  SELECT * FROM
    (
     SELECT
       DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,
       end.READING,
       (end.VALUE-begin.VALUE) AS VALUE
     FROM
     (
         SELECT
           h.TIMESTAMP,
           h.READING,
           cast(h.VALUE/1000 AS decimal(6)) AS VALUE
         FROM history h
       INNER JOIN
         (
         SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
           WHERE §device§ AND §reading§
          	 AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' ) 
             AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' )
           GROUP BY READING
         ) x1
       ON    h.TIMESTAMP = x1.TIMESTAMP
         AND h.READING   = x1.READING
         AND h.VALUE != 0
     ) end
   INNER JOIN
     (	
       SELECT
         h.TIMESTAMP,
         h.READING,
         if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE
       FROM history h
     INNER JOIN
       (
        SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
        	AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' ) 
            AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )
          GROUP BY READING
       ) x1
     ON    h.TIMESTAMP = x1.TIMESTAMP
       AND h.READING   = x1.READING
       AND h.VALUE != 0
     ) begin
   ON    end.READING = begin.READING
     AND MONTH(end.TIMESTAMP) IN (3,6,9,12)
   ) QX
) QC

UNION ALL

SELECT
  TIMESTAMP,
  IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,
  VALUE
FROM
(
  SELECT
    DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,
    '_Year' AS READING,
	null    AS VALUE
  UNION ALL
  SELECT * FROM
    (
     SELECT
       DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP,
       end.READING,
       (end.VALUE-begin.VALUE) AS VALUE
     FROM
     (
         SELECT
           h.TIMESTAMP,
           h.READING,
           cast(h.VALUE/1000 AS decimal(6)) AS VALUE
         FROM history h
       INNER JOIN
         (
         SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
         	 AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' ) 
             AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' )
           GROUP BY READING
         ) x1
       ON    h.TIMESTAMP = x1.TIMESTAMP
         AND h.READING   = x1.READING
         AND h.VALUE != 0
     ) end
   INNER JOIN
     (	
       SELECT
         h.TIMESTAMP,
         h.READING,
         if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE
       FROM history h
     INNER JOIN
       (
        SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE §device§ AND §reading§
        	AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 13+@offset MONTH, '%Y/%m/28' ) 
            AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 12+@offset MONTH, '%Y/%m/01' )
          GROUP BY READING
       ) x1
     ON    h.TIMESTAMP = x1.TIMESTAMP
       AND h.READING   = x1.READING
       AND h.VALUE != 0
     ) begin
   ON    end.READING = begin.READING
     AND MONTH(end.TIMESTAMP) IN (3,6,9,12)
   ) QX
) QD
;

Im Device ist dann die Reihenfolge wie folgt zu lesen Q1, Q4, Q3, Q2 . Das Q1 ist mit previous markiert, da wir ja jetzt im Q2 sind.

VG
Christian

majestro84 · 08 April 2022, 19:11:32

Zitat von: ch.eick am 08 April 2022, 18:01:37
Hallo Alex,
bitte teste das SQL SELECT dann nochmal mit Deinen Daten, es sollte jetzt so passen.

SqlCmd für das DbRep Device LogDBRep_Statistic_previous_Quarter
Code Auswählen Erweitern
SELECT * FROM ( SELECT TIMESTAMP, IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING, VALUE FROM ( SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP, '_Year' AS READING, 'previous' AS VALUE UNION ALL SELECT * FROM ( SELECT DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP, end.READING, (end.VALUE-begin.VALUE) AS VALUE FROM ( SELECT h.TIMESTAMP, h.READING, cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL @offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) end INNER JOIN ( SELECT h.TIMESTAMP, h.READING, if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12) ) QX ) QA ) UA UNION ALL SELECT TIMESTAMP, IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING, VALUE FROM ( SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP, '_Year' AS READING, null AS VALUE UNION ALL SELECT * FROM ( SELECT DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP, end.READING, (end.VALUE-begin.VALUE) AS VALUE FROM ( SELECT h.TIMESTAMP, h.READING, cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) end INNER JOIN ( SELECT h.TIMESTAMP, h.READING, if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12) ) QX ) QB UNION ALL SELECT TIMESTAMP, IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING, VALUE FROM ( SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP, '_Year' AS READING, null AS VALUE UNION ALL SELECT * FROM ( SELECT DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP, end.READING, (end.VALUE-begin.VALUE) AS VALUE FROM ( SELECT h.TIMESTAMP, h.READING, cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 7+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 6+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) end INNER JOIN ( SELECT h.TIMESTAMP, h.READING, if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12) ) QX ) QC UNION ALL SELECT TIMESTAMP, IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING, VALUE FROM ( SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP, '_Year' AS READING, null AS VALUE UNION ALL SELECT * FROM ( SELECT DATE_FORMAT(end.TIMESTAMP, '%Y-%m-%d 23:59:00') AS TIMESTAMP, end.READING, (end.VALUE-begin.VALUE) AS VALUE FROM ( SELECT h.TIMESTAMP, h.READING, cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 10+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 9+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) end INNER JOIN ( SELECT h.TIMESTAMP, h.READING, if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN ( SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 13+@offset MONTH, '%Y/%m/28' ) AND TIMESTAMP < DATE_FORMAT( CURRENT_DATE - INTERVAL 12+@offset MONTH, '%Y/%m/01' ) GROUP BY READING ) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0 ) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12) ) QX ) QD ;
Im Device ist dann die Reihenfolge wie folgt zu lesen Q1, Q4, Q3, Q2 . Das Q1 ist mit previous markiert, da wir ja jetzt im Q2 sind.

VG
Christian

Hallo Christian
Ich habe es versucht mit dem SELECT leider ohne Erfolg.
Wenn ich es auf dem SQL Server eingeben hängt es an Folgender Stelle

Code Auswählen

§device§ AND §reading§

Ist das so gewollt?

Die Frage hat sich geklärt das sind die Attribute aus dem LogDBRep_Statistic_previous_Quarter Device

Im FHEM läuft die Abfrage durch allerdings ohne Ergebnis.

VG Alex

ch.eick · 09 April 2022, 08:19:12

Zitat von: majestro84 am 08 April 2022, 19:11:32
Ich habe es versucht mit dem SELECT leider ohne Erfolg.
Im FHEM läuft die Abfrage durch allerdings ohne Ergebnis.

Hallo zusammen

wenn bei dieser DbRep Abfrage nur die Quartals Namen zurück geliefert werden, dann liegt das daran, dass in der Datenbank keine Daten vorhanden sind.

Code Auswählen


+---------------------+---------+----------+
| TIMESTAMP           | READING | VALUE    |
+---------------------+---------+----------+
| 2022-03-31 23:59:00 | Q1      | previous |
| 2021-12-31 23:59:00 | Q4      | NULL     |
| 2021-09-30 23:59:00 | Q3      | NULL     |
| 2021-06-30 23:59:00 | Q2      | NULL     |
+---------------------+---------+----------+
4 rows in set (0.331 sec)

Für die Berechnung werden die letzten Einträge im vorherigen und im aktuellen Quartal benötigt.

Offset sezten

Code Auswählen


SET @offset:= ( 
  CASE WHEN MONTH(CURRENT_DATE) IN (1,4,7,10) THEN @offset:=0  
       WHEN MONTH(CURRENT_DATE) IN (2,5,8,11) THEN @offset:=1
       ELSE @offset:=2
  END
 );

Start Werte des Quartals

Code Auswählen


SELECT
         h.TIMESTAMP,
         h.READING,
         if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE
       FROM history h
     INNER JOIN
       (
        SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
          WHERE  DEVICE = 'WR_1_API'
            AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )
            AND READING NOT LIKE '%Autarky%'
            AND READING NOT LIKE '%Rate%'
            AND READING NOT LIKE '%NoBat%'
        	AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 4+@offset MONTH, '%Y/%m/28' ) 
            AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL 3+@offset MONTH, '%Y/%m/01' )
          GROUP BY READING
       ) x1
     ON    h.TIMESTAMP = x1.TIMESTAMP
       AND h.READING   = x1.READING
       AND h.VALUE != 0
 ;

Ende Werte des Quartals

Code Auswählen


SELECT
           h.TIMESTAMP,
           h.READING,
           cast(h.VALUE/1000 AS decimal(6)) AS VALUE
         FROM history h
       INNER JOIN
         (
         SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history
           WHERE DEVICE = 'WR_1_API'
             AND ( READING LIKE 'SW_Statistic_%Year' OR READING='Statistic_EnergyHomeBat_Year' )
             AND READING NOT LIKE '%Autarky%'
             AND READING NOT LIKE '%Rate%'
            AND READING NOT LIKE '%NoBat%'
          	 AND TIMESTAMP >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1+@offset MONTH, '%Y/%m/28' ) 
             AND TIMESTAMP <  DATE_FORMAT( CURRENT_DATE - INTERVAL @offset MONTH, '%Y/%m/01' )
           GROUP BY READING
         ) x1
       ON    h.TIMESTAMP = x1.TIMESTAMP
         AND h.READING   = x1.READING
         AND h.VALUE != 0
 ;

Nun kann man manuell die fehlenden Werte eintragen.
Anstelle der Nullen kann man natürlich auch die richtigen Werte eintragen.

Code Auswählen


INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','Statistic_EnergyHomeBat_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyHome_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyHomeFeedInGrid_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyHomeGrid_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyHomePv_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyHomePvSum_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyPv1_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyPv2_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyPv4_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_EnergyPv5_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_TotalConsumption_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-12-31 23:57:03','SW_Statistic_Yield_Year',0);

Wenn oben die Jahres End Werte eingetragen sind und man hier zusätzlich alles auf Null setzt, dann sollten auch für Q4 Werte angezeigt werden.

Code Auswählen


INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','Statistic_EnergyHomeBat_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyHome_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyHomeFeedInGrid_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyHomeGrid_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyHomePv_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyHomePvSum_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyPv1_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyPv2_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyPv4_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_EnergyPv5_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_TotalConsumption_Year',0);
INSERT INTO `history` (`TIMESTAMP`,`READING`,`VALUE`) VALUES ('2021-09-31 23:57:03','SW_Statistic_Yield_Year',0);

Sollte im WR_1_API das Quartal nicht sofort angezeigt werden, so muss dies einmal aktualisiert werden. Das kann man einfach über das PV_Schedule Device erreichen, indem man "Bilanz cmd_4" ausführt.

VG
Christian

kaiman · 12 April 2022, 13:57:35

Hallo zusammen,

ich kämpfe gerade auf mit den Quartalswerten.

Den DB Timeout hab ich jetzt schon auf 2 Tage gestellt, aber ich bekomm immer ein "LogDBRep_Statistic_previous_Quarter

Timeout: process terminated,"

Hab den Timeout jetzt auf 3 Tage gestellt und noch einmal angestoßen.

DS_Starter · 12 April 2022, 14:01:57

Falls die timeouts erst seit einem FHEM update auftreten und sonst auch evtl. Netzwerkprobleme im Log zu sehen sein sollten schau mal in diesen Thread -> https://forum.fhem.de/index.php/topic,127077.0.html

ch.eick · 12 April 2022, 14:13:50

Zitat von: kaiman am 12 April 2022, 13:57:35
Hallo zusammen,

ich kämpfe gerade auf mit den Quartalswerten.

Den DB Timeout hab ich jetzt schon auf 2 Tage gestellt, aber ich bekomm immer ein "LogDBRep_Statistic_previous_Quarter

Timeout: process terminated,"

Hab den Timeout jetzt auf 3 Tage gestellt und noch einmal angestoßen.

Okay, bei den timeouts kann ich nicht wirklich helfen, die habe ich bei mir nicht.

Wenn es Fragen zum Quartals Report gibt, da haben wir bereits per PN einige Erfahrungen gesammelt. Melde Dich einfach.

VG Christian

ch.eick · 07 Mai 2022, 21:36:56

Hallo,
kann das sein, dass alles so stabiel läuft, oder sind hier alle geflüchtet

?

Meine Prognose lief für heute ziemlich genau, 97 kWh Prognose zu 103 kWh Ertrag

und das alles ohne Autokorrektur. Die habe ich bereits seit Januar ausgeschaltet, um mal zu schauen, wie es ohne laufen würde.

Wie läuft es so bei Euch, das kann hier auch zu einem Kostal Stammtisch werden. Eventuell entstehen ja noch neue Ideen.

VG
Christian

zwölfgang · 08 Mai 2022, 19:03:31

Hallo Christian,
nein nein, nicht geflüchtet

, nur zufrieden und es läuft tatsächlich bei mir ganz gut. Ich versuche grad noch den go-eCharger und eine Warmwasserwärmepumpe in deinem Stil zu integrieren, und da hängt die Messlatte schon hoch. Ich verfolge das alles gerne und bin immer noch dabei.

viele Grüße
Wolfgang

PS. hab mal noch einen kleinen screen von heute angehängt, die Zahlen können sich glaub schon sehen lassen.