Fixing values in rainRate table

[Thomas Carlin]

Hi All! I've been playing with some skin modifications on my weather station again, and found some strange values in a few tables.  Most of them i have been able to clean up without any issue, but I feel like there is more to the rainRate table.  

I found the following table of values, obviously the min and max are false.  I can't however reason out how, if at all the sum, count, wsum relate to the other values.  

What is the best way to clear out these bad values?  I am guessing on some of the min values, I already ran an update command before pulling this full list, and because i live dangerously, didn't pull a backup first, but i do know that the first 10 entries are 100 percent accurate.

sqlite> select * from archive_day_rainRate where max >10;
dateTime|min|mintime|max|maxtime|sum|count|wsum|sumtime
1605164400|0.0|1605164402|655.350000000001|1605188100|2136.40082144|287|640920.246432|86100
1618898400|0.0|1618898403|655.350000000001|1618930800|26711.4767995|288|8013443.03985|86400
1643439600|0.0|1643439601|655.350000000001|1643465400|2551.68191489362|244|765504.574468086|73200
1645513200|0.0|1645513203|655.350000000001|1645584600|31106.9906622517|287|9332097.19867551|86100
1645599600|0.0|1645599603|655.350000000001|1645680600|8779.61978259878|288|2633885.93477963|86400
1662703200|0.0|1662703202|655.350000000001|1662706200|40020.4846440398|288|12006145.3932119|86400
1668150000|0.0|1668150004|655.350000000001|1668153600|8030.40075523906|254|2409120.22657172|76200
1668754800|655.35|1668817498|655.35|1668817498|655.35|1|196605.0|300
1668841200|655.35|1668856799|655.35|1668856799|1966.05|3|589815.0|900
1668927600|655.35|1668940798|655.35|1668940798|2621.4|4|786420.0|1200
1669014000|655.35|1669023898|655.35|1669023898|3932.1|6|1179630.0|1800
1669100400|655.35|1669109698|655.35|1669109698|4587.45|7|1376235.0|2100
1669186800|655.35|1669187699|655.35|1669187699|6553.5|10|1966050.0|3000
1669273200|655.35|1669278900|655.35|1669278900|7208.85|11|2162655.0|3300
1669359600|655.35|1669365300|655.35|1669365300|7864.2|12|2359260.0|3600
1669446000|655.35|1669451697|655.35|1669451697|7208.85|11|2162655.0|3300
1669532400|655.35|1669536899|655.35|1669536899|9830.25|15|2949075.0|4500
1669618800|655.35|1669620000|655.35|1669620000|9174.9|14|2752470.0|4200
1669705200|655.35|1669719897|655.35|1669719897|9174.9|14|2752470.0|4200
1669791600|655.35|1669793098|655.35|1669793098|11140.95|17|3342285.0|5100
1669878000|655.35|1669879500|655.35|1669879500|13107.0|20|3932100.0|6000
1669964400|655.35|1669966800|655.35|1669966800|10485.6|16|3145680.0|4800
1670050800|655.35|1670052899|655.35|1670052899|7864.2|12|2359260.0|3600
1670137200|655.35|1670194711|655.35|1670138996|7864.2|109|2359260.0|32700
1670396400|0.0|1670396403|655.350000000001|1670407500|37676.1049695061|287|11302831.4908518|86100
1672297200|0.0|1672297201|655.350000000001|1672323300|22253.0669985626|287|6675920.09956877|86100
1676098800|0.0|1676098802|655.350000000001|1676113800|28772.4393132542|288|8631731.79397624|86400
1676185200|0.0|1676185202|655.350000000001|1676209500|13341.363576159|268|4002409.07284769|80400
1676703600|0.0|1676703602|655.350000000001|1676716500|8775.40582191782|287|2632621.74657535|86100
1698991200|0.0|1698991202|655.350000000001|1699011600|19847.6247601028|288|5954287.42803082|86400
1699686000|0.0|1699686004|655.350000000001|1699711200|17135.218|287|5140565.40000001|86100
1701586800|0.0|1701586804|655.35|1701627947|15.2406976744186|287|4572.20930232558|86100

Your thoughts, expertise and software is appreciated!

[Thomas Carlin]

I also ran the queries below.  Obviously all the archive records that relate to the records in my original post need to be updated, but is it safe to assume that all of the records that are in these queries will need to be updated as well, or is there a case where rainRate > 0 but rain=0.0?

sqlite> select count(*) from archive where rainRate > 0 and rain=0.0;
count(*)
4464
sqlite> select count(*) from archive where rainRate > 0 and rain is NULL;
count(*)
185
sqlite> select count(*) from archive where rain is NULL;
count(*)
317

These are easy, I expect, to fix, i expect.  Simply:

update archive set rain=0.0 where rain is NULL;

update archive set rainRate = 0 where rainRate > 0 AND rain=0.0;

Is there a better way to fix all these things in one shot?

Thanks again!

[vince]

Each will be so fast running a couple commands won’t take long..

[Thomas Carlin]

Is there any case where rainRate will be > 0, but rain = 0 for a time period?

What do  sum, count, wsum, sumtime account for in the rainRate table?

I suspect that if these values are off in the archive table, is a safe to assume that they are off in the archive_day_rain table as well?  Should i rebuild the daily summaries as part of this cleanup also?

Thanks,

[michael.k...@gmx.at]

> Is there any case where rainRate will be > 0, but rain = 0 for a time period?

Yes, if the archive_interval is shorter than the interval used for the rain rate calculation, this is always the case after the last archive_interval with rain of a single rain event.

> What do  sum, count, wsum, sumtime account for in the rainRate table?

The same as for all other observation types, one thing is making calculate statistics faster. When you fix the rainRate values in the archive table, you should rebuild the summaries also.