How to correct a reset of a increasing value in MySql Table

Question

Im using a MySql table, to save sensor values I collect over RS485. The sensor sums up the charge, that has already gone into the battery. It's positive when discharged and negative when charged.

Sadly I reset the measuring module on accident.. So my charge drops 161 A/h from one row to the other, and new data is related to that zero point. HERE you can see my table structure with the reset value for charge, work and time. The row where the incident happened is highlighted in blue.

How can i correct that reset the best way? I thought of creating a table with resetId(s) as index (for the case when it happens again) and offsets for the colums. But I havend yet found a way to integrate that into my queries.

I use the data to do statistics. Most of all charge difference between hours or days or a Top10 for the days with the best charge. Example query for dayStats (for a time before the incident) is shown below:

SELECT 
   id                                                        AS _cid, 
   curtime                                                   AS mynow, 
   Date_format(curtime, '%H%:00')                            AS date, 
   Round(Min(CURRENT) / 10, 2)                               AS 'min current', 
   Round(Avg(CURRENT) / 10, 2)                               AS 'avg current', 
   Round(Max(CURRENT) / 10, 2)                               AS 'max current', 
   Round(Min(power) / 1000, 2)                               AS 'min power', 
   Round(Avg(power) / 1000, 2)                               AS 'avg power', 
   Round(Max(power) / 1000, 2)                               AS 'max power', 
   charge / 1000                                             AS 'charge', 
   Round(( ( (SELECT charge 
              FROM   MeasurementData.SolarPower 
              WHERE  Max(_cid) = id) - charge ) / 1000 ), 2) AS chgDiff 
   FROM   MeasurementData.SolarPower 
   WHERE  Day(curtime) = Day('2018-05-05 12:00:00') 
   GROUP  BY Hour(curtime) 
   ORDER  BY mynow DESC 

This would be a normal output for day statistics

The week stats are almost the same query. But here the reset shows its effetc..

    SELECT 
       id                                                        AS _cid, 
       curtime                                                   AS mynow, 
       Date_format(curtime, '%d%.%m.%Y')                         AS date, 
       Date_format(curtime, '%W')                                AS weekday, 
       Round(Min(CURRENT) / 10, 2)                               AS 'cur-min', 
       Round(Avg(CURRENT) / 10, 2)                               AS 'cur-avg', 
       Round(Max(CURRENT) / 10, 2)                               AS 'cur-max', 
       Round(Min(power) / 1000, 2)                               AS 'pow-min', 
       Round(Avg(power) / 1000, 2)                               AS 'pow-avg', 
       Round(Max(power) / 1000, 2)                               AS 'pow-max', 
       Round((SELECT charge 
              FROM   MeasurementData.SolarPower
              WHERE  Max(_cid) = id) / 1000, 2)                  AS chg, 
       Round((((SELECT charge 
                  FROM   MeasurementData.SolarPower
                  WHERE  Max(_cid) = id) - charge ) / 1000 ), 2) AS chgDiff 
FROM   MeasurementData.SolarPower
WHERE  curtime >= Date_sub('2018-05-05 12:00:00', interval 6 day) 
GROUP  BY DATE 
ORDER  BY mynow DESC 

What outputs to: THAT Here the reset incident is clearly visible.

By the way ... I'm not too good in that SQL stuff, and the queries shown are pretty slow. Any suggestions on how to improve them?

Thank you guys in advance.

Answer 1

You have determined that a particular value in the table is 'incorrect'? Then use an UPDATE statement with a WHERE clause to specify the particular row to change.

You have determined that a particular column in the table, for a certain time range, needs to be adjusted by some constant value? Then use an UPDATE statement with a WHERE clause to specify the particular rows to change.