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/2967d398-c309-406b-a075-ba7e554887a9n%40googlegroups.com.
