[vz-users] Aggregation (minute) schlägt fehl

[Jens Scheidtmann]

Hallo Michael,

Um die Datensätze, die den Fehler verursachen, einzugrenzen öffne bitte
mysql als root und gebe folgende Befehle ein:

$ sudo mysql
> use volkszaehler;

Dann kopiere dieses Statement hier ein:

--- schnipp ---
  SELECT channel_id
       , 1 AS type
       , MAX(agg.timestamp) AS timestamp
       , SUM(agg.val_by_time) as s
       , MAX(agg.timestamp) as t_max
       , MIN(agg.prev_timestamp) as t_min
       , MAX(agg.timestamp) - MIN(agg.prev_timestamp) as delta
       , COUNT(agg.value) AS count
    FROM ( SELECT channel_id
                , timestamp
                , value
                , value * (timestamp - @prev_timestamp) AS val_by_time
                , COALESCE(@prev_timestamp, 0) AS prev_timestamp
                , @prev_timestamp := timestamp
             FROM data CROSS JOIN (SELECT @prev_timestamp :=
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
                                     FROM aggregate
                                    WHERE type = 1
                                      AND aggregate.channel_id = "3") AS
vars
            WHERE channel_id = "3"
            AND timestamp >= IFNULL((SELECT
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
                                       FROM aggregate
                                      WHERE type = 1
                                        AND aggregate.channel_id = "3" ), 0)

            AND timestamp < UNIX_TIMESTAMP(DATE_FORMAT(NOW(), "%Y-%m-%d
%H:%i:00")) * 1000 ) AS agg
   GROUP BY channel_id
       , YEAR(FROM_UNIXTIME(timestamp/1000))
       , DAYOFYEAR(FROM_UNIXTIME(timestamp/1000))
       , HOUR(FROM_UNIXTIME(timestamp/1000))
       , MINUTE(FROM_UNIXTIME(timestamp/1000))
ORDER BY 1,2,3,4,5
    INTO OUTFILE '/var/lib/mysql/offending.csv'
         FIELDS TERMINATED BY ','
         ENCLOSED BY '"'
        LINES TERMINATED BY '\n'   ;
--- schnapp ---
Wenn Du uns die Werte nicht verraten möchtest, kannst Du "value" am Anfang
durch "0" ersetzen.

Zeige uns bitte die Tabelle aus /var/lib/mysql/offending.csv!

Dort musst Du dann die Zeile finden, die in der vorletzten Spalte eine "0"
hat.

In den Zeilen davor und danach findest Du die timestamps, die Du im
folgenden Statement für ****MIN**** und ****MAX**** einsetzen musst:
--- schnipp ---
SELECT channel_id
    , timestamp
    , value
    , value * (timestamp - @prev_timestamp) AS val_by_time
    , COALESCE(@prev_timestamp, 0) AS prev_timestamp
    , @prev_timestamp := timestamp
    FROM data CROSS JOIN (SELECT @prev_timestamp :=
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
                            FROM aggregate
                        WHERE type = 1
                            AND aggregate.channel_id = "3") AS vars
WHERE channel_id = "3"
AND timestamp >= ****MIN****
AND timestamp < ****MAX****
   INTO OUTFILE '/var/lib/mysql/rawdata.csv'
         FIELDS TERMINATED BY ','
         ENCLOSED BY '"'
        LINES TERMINATED BY '\n'   ;
--- schnapp ---
Wenn Du uns die Werte nicht verraten möchtest, kannst Du die zwei "value"
am Anfang durch "0" ersetzen.
Auch diese Datei solltest Du anhängen.
-------------- nächster Teil --------------
Ein Dateianhang mit HTML-Daten wurde abgetrennt...
URL: <http://demo.volkszaehler.org/pipermail/volkszaehler-users/attachments/20230324/e9959dd5/attachment.html>