"Your DB is messed up", so what do you mean? Is it corrupt or is there just a lot of bad data? I am aware of that there is bad data, hence the reasoning of the original post.
When you say "fix that, rebuild-daily" are you referring to setting all the values for "rainRate" over the value of 5 to NULL? I assume that I would need to manually do a rebuild-daily for each date individually or can that be scripted with all the dates of the bad values? On Monday, November 3, 2025 at 2:36:39 PM UTC-6 vince wrote: > So your db is messed up. Fix that, rebuild-daily for the affected dates. > You should be ok then. > > You might need to run the query a few times or specify more than 10 days > to get all the bad days identified. Perhaps remove the limit 10 to see if > you have a very lot of bad records in there… > > On Monday, November 3, 2025 at 12:16:16 PM UTC-8 S Phillips wrote: > >> Stopped Weewx >> sudo systemctl stop weewx >> >> Made a copy of the existing DB >> sudo cp /var/lib/weewx/weewx.sdb weewx_20251103_1338_bad.sdb.bak >> >> Copied the old version prior to values being changed to NULL >> sudo cp /home/<username>/Documents/weewx_20251102_1851.sdb >> /var/lib/weewx/weewx.sdb >> >> Started WeeWX to maintain current data while old data is being modified >> sudo systemctl start weewx >> sudo systemctl status weewx >> >> I copied the copy of "weewx_20251102_1851.sdb" file back down to my >> Macbook using Filezilla to get a "fresh start". Then looked at Vince's >> latest comment at 13:38 CST and ran the SQL queries. >> >> The first query results the following: >> >> SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, max from >> archive_day_rainRate where max>2 ORDER BY max DESC LIMIT 10; >> >> >> >> >> >> >> >> >> >> >> >> *datetime(dateTime,'unixepoch','localtime') dateTime max 2017-09-18 >> 00:00:00 1505710800 84.6236220472441 2017-09-22 00:00:00 1506056400 >> 84.6047244094488 2017-09-23 00:00:00 1506142800 84.6047244094488 2017-09-26 >> 00:00:00 1506402000 84.6047244094488 2017-09-27 00:00:00 1506488400 >> 84.6047244094488 2019-10-11 00:00:00 1570770000 82.29 2020-05-17 00:00:00 >> 1589691600 82.29 2021-06-24 00:00:00 1624510800 82.29 2022-07-08 00:00:00 >> 1657256400 82.29 2023-08-10 00:00:00 1691643600 82.29* >> >> Second query: >> >> SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, sum from >> archive_day_rain where sum>2 ORDER BY sum DESC LIMIT 10; >> >> >> >> >> >> >> >> >> >> >> >> *datetime(dateTime,'unixepoch','localtime') dateTime sum 2016-08-12 >> 00:00:00 1470978000 14.18 2016-07-24 00:00:00 1469336400 12.37 2016-10-06 >> 00:00:00 1475730000 4.96 2018-03-17 00:00:00 1521262800 4.90157480314961 >> 2016-06-01 00:00:00 1464757200 4.43 2016-08-15 00:00:00 1471237200 4.41 >> 2016-07-25 00:00:00 1469422800 3.69 2016-08-28 00:00:00 1472360400 3.35 >> 2016-09-08 00:00:00 1473310800 2.88 2016-07-14 00:00:00 1468472400 2.87* >> >> Third query: >> >> SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, rainRate >> from archive where rainRate>2 ORDER BY rainRate DESC LIMIT 10; >> >> >> >> >> >> >> >> >> >> >> >> *datetime(dateTime,'unixepoch','localtime') dateTime rainRate 2017-09-18 >> 04:50:00 1505728200 84.6236220472441 2017-09-18 07:45:00 1505738700 >> 84.6236220472441 2017-09-22 05:15:00 1506075300 84.6047244094488 2017-09-23 >> 10:20:00 1506180000 84.6047244094488 2017-09-26 17:00:00 1506463200 >> 84.6047244094488 2017-09-27 04:40:00 1506505200 84.6047244094488 2019-10-11 >> 16:55:00 1570830900 82.29 2020-05-17 12:05:00 1589735100 82.29 2021-06-24 >> 21:25:00 1624587900 82.29 2022-07-08 08:05:00 1657285500 82.29* >> >> On Monday, November 3, 2025 at 1:38:22 PM UTC-6 vince wrote: >> >>> Lets go back to square one. What does the database show ? If that's >>> still not correct, nothing related to graphs or html output matters. >>> >>> Belchertown is unusual... >>> >>> - It also has its 'own' NOAA output directory in 'addition to' the >>> normal one weewx skins generate, so if you're going to do things like >>> clearing out previously generated NOAA files for month(s) or year(s), >>> make >>> sure to get them in all locations under /var/www/html or wherever the >>> web >>> docroot is set to. >>> - It does a lot of sqlite queries under the hood to generate its >>> data that winds up in the html >>> - those alltime table entries come from db queries in belchertown.py >>> around line 780 or so if you wanted to see it in the extension python >>> code >>> >>> We need to see db queries of the rain-related archive and summary >>> tables.... >>> >>> # highest 10 summary table days where rainRate > 2 sorted highest to >>> lowest >>> echo "SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, max >>> from archive_day_rainRate where max>2 ORDER BY max DESC LIMIT 10;" | >>> sqlite3 mydbname.sdb >>> >>> # highest 10 summary table days where rain for the day > 2 sorted >>> highest to lowest >>> echo "SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, sum >>> from archive_day_rain where sum>2 ORDER BY sum DESC LIMIT 10;" | sqlite3 >>> mydbname.sdb >>> >>> # highest 10 archive table records where rainRate > 2 sorted highest to >>> lowest >>> echo "SELECT datetime(dateTime,'unixepoch','localtime'), dateTime, >>> rainRate from archive where rainRate>2 ORDER BY rainRate DESC LIMIT 10;" | >>> sqlite3 mydbname.sdb >>> >>> For the original poster.... >>> >>> - be sure to work off a 'copy' of your current database, >>> just-in-case.... >>> - please use the commandline on your pi for this - just substitute >>> in the filename of your temporary copy of the db >>> - if you don't have the sqlite3 utility on your pi, you can install >>> it via "sudo apt install sqlite3" >>> - I used '2' above which is a good number for my location since we >>> don't get much/heavy rain. Feel free to use whatever works for you >>> there. >>> >>> The offer still stands for me to verify your db is ok if you can make >>> your db available someplace for download.... >>> >>> On Monday, November 3, 2025 at 10:22:45 AM UTC-8 Jeff A. D. wrote: >>> >>>> All affected reports, including NOAA Climatological Summaries and such, >>>> will also need to be deleted and rebuilt, as Tom says. Also note that if >>>> all you did was NULL the rain data for each archive period that showed >>>> rain, and not for the entire period (day, month, etc.) that had the bad >>>> data, your reports will still show 0 (instead of N/A) for the day. >>>> >>>> On Monday, November 3, 2025 at 7:44:13 AM UTC-7 Tom Keffer wrote: >>>> >>>>> Plot images are renewed only as often as their aggregation interval. >>>>> You may just be looking at your old data. Delete all the old images and >>>>> try >>>>> again. >>>>> >>>>> On Sun, Nov 2, 2025 at 6:47 PM S Phillips <[email protected]> wrote: >>>>> >>>>>> After reviewing the comments to the orginal post and my follow-up >>>>>> comment, I performed the following tasks: >>>>>> >>>>>> I made a copy of the current DB to my home directory with: >>>>>> *sudo cp /var/lib/weewx/weewx.sdb >>>>>> /home/<username>/Documents/weewx_20251028_1731.sdb* >>>>>> >>>>>> I then copied the file to my Macbook via SFTP using Filezilla. I >>>>>> opened the .sdb file in DB Browser for SQLite, then ran the following >>>>>> command: >>>>>> *UPDATE archive SET rainRate=NULL and rain=NULL WHERE (rainRate > 5);* >>>>>> >>>>>> It returned the following: >>>>>> >>>>>> *Execution finished without errors.* >>>>>> *Result: query executed successfully. Took 63ms, 83 rows affected* >>>>>> *At line 1:* *UPDATE archive SET rainRate=NULL and rain=NULL WHERE >>>>>> (rainRate > 5);* >>>>>> >>>>>> After that was finished I performed a "Write Changes" from the DB >>>>>> Browser for SQLite and saved the file with the new timestamp name. Next >>>>>> I >>>>>> copied the file back to my home directory on the WeeWX VM via SFTP in >>>>>> FileZilla. I then stopped the DB using: >>>>>> *sudo systemctl stop weewx* >>>>>> >>>>>> Then I copied the latest sdb from the /var/lib directory as a backup. >>>>>> *sudo cp /var/lib/weewx/weewx.sdb weewx_20251102_1907.sdb.bak* >>>>>> >>>>>> Once that was done, I copied the edited sbd back to the /var/lib >>>>>> directory using the following: >>>>>> *sudo cp /home/<username>/Documents/weewx_20251102_1851.sdb >>>>>> /var/lib/weewx/weewx.sd <http://weewx.sd>**b* >>>>>> >>>>>> I then dropped the daily and rebuilt it using the following: >>>>>> >>>>>> *sudo weectl database drop-daily* *sudo weectl database >>>>>> rebuild-daily* >>>>>> >>>>>> After that was complete, I started WeeWX back up using >>>>>> *sudo systemctl start weewx* >>>>>> >>>>>> After it did an upload to the webserver, I checked the records page >>>>>> and the bad values are still listed. When I look for any rainRate values >>>>>> over 4.9, it returns one result. Thoughts? >>>>>> >>>>>> [image: Screenshot 2025-11-02 at 20.40.40.png] >>>>>> >>>>>> >>>>>> [image: Bad Records.png] >>>>>> >>>>>> On Friday, October 31, 2025 at 11:03:09 AM UTC-5 vince wrote: >>>>>> >>>>>>> I can see either answer in this case. Agree with Mark about NULL >>>>>>> vs. zero. Tom's words in the wiki recommend NULL (link >>>>>>> <https://github.com/weewx/weewx/wiki/Cleaning-up-old-bad-data>). >>>>>>> >>>>>>> On Friday, October 31, 2025 at 3:31:16 AM UTC-7 Mark Jenks wrote: >>>>>>> >>>>>>>> That is exactly what I've done in the past. Just find the bad >>>>>>>> data and NULL it out. NULL says no data, 0 says no rain. There >>>>>>>> is a >>>>>>>> difference. >>>>>>>> >>>>>>>> There is no good reason to edit it to try and figure out what it >>>>>>>> was, unless there was some huge event that you failed to capture >>>>>>>> accurately. >>>>>>>> >>>>>>>> On Thursday, October 30, 2025 at 12:54:31 PM UTC-5 Jeff A. D. wrote: >>>>>>>> >>>>>>>>> For the sake of accuracy, I think the quickest and easiest way >>>>>>>>> would be to just go through and select all the dates with >>>>>>>>> questionable data >>>>>>>>> in the database and set all the rain and rain rate data to null, >>>>>>>>> rather >>>>>>>>> than zero, and then rebuild dailies. That should tell you you have >>>>>>>>> no data >>>>>>>>> for those times, rather than indicating no rain. (It should show >>>>>>>>> "N/A", >>>>>>>>> rather than 0, for those dates on the Climatological Summary.) >>>>>>>>> >>>>>>>>> On Thursday, October 30, 2025 at 9:44:57 AM UTC-6 vince wrote: >>>>>>>>> >>>>>>>>>> I would just identify which 5-minute archive periods have bad >>>>>>>>>> data, then zero out the rain and rainRate fields out for those >>>>>>>>>> 5-minute >>>>>>>>>> period records. That would be close enough for me. You seem to >>>>>>>>>> have >>>>>>>>>> something far more complicated in mind, so best of luck. >>>>>>>>>> >>>>>>>>>> On Thursday, October 30, 2025 at 4:18:31 AM UTC-7 S Phillips >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> So the data which I need to focus on is the rain data that is >>>>>>>>>>> held in the archive table and once I can determine the bad values I >>>>>>>>>>> can >>>>>>>>>>> then rebuilt the daily which should correct the issue. Since I >>>>>>>>>>> live so >>>>>>>>>>> close to where the official readings are kept (~1.5 miles) I can >>>>>>>>>>> use that >>>>>>>>>>> data as a reference. I know that there will be variation but >>>>>>>>>>> extremes >>>>>>>>>>> differences should be easy to spot. For example, here is July 2016 >>>>>>>>>>> from >>>>>>>>>>> NOAA and my PWS where you can see the extreme variations. >>>>>>>>>>> >>>>>>>>>>> [image: Combined 2016-07 copy.png] >>>>>>>>>>> On Wednesday, October 29, 2025 at 7:52:27 PM UTC-5 vince wrote: >>>>>>>>>>> >>>>>>>>>>>> Forgot to answer your question - if you rebuilt-daily then your >>>>>>>>>>>> bad data is in the archive table (which is used to generate the >>>>>>>>>>>> summary >>>>>>>>>>>> table) >>>>>>>>>>>> >>>>>>>>>>>> Expecting your local rain total in an extreme event to match >>>>>>>>>>>> anybody else is a bad idea. Microclimates can have different >>>>>>>>>>>> answers >>>>>>>>>>>> across the street from the other station, let alone from one miles >>>>>>>>>>>> away. >>>>>>>>>>>> >>>>>>>>>>>> You certainly can fix up the rainRate item in your archive >>>>>>>>>>>> table, or at least zero it out, but I would suspect your rain >>>>>>>>>>>> field (rain >>>>>>>>>>>> in that usually 5 minute period) likely needs similar cleanup. >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "weewx-user" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to [email protected]. >>>>>> >>>>> To view this discussion visit >>>>>> https://groups.google.com/d/msgid/weewx-user/3f27c2db-4d1b-4fe3-86b1-f34c9420fe20n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/weewx-user/3f27c2db-4d1b-4fe3-86b1-f34c9420fe20n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/6eb6f473-ae03-43f2-9847-7ac03b1e54e2n%40googlegroups.com.
