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/e6547630-9663-4173-9f80-1a2fefbcd65fn%40googlegroups.com.
