So just for clarification, the version that I ran the queries on was the backup version prior to setting the bad values to NULL. I reverted back to this version based on your comment on 11/2 @ 21:21 CST where you mentioned the following:
*"rainRate not rainrate in your image FWIW* *I would redo your queries off your working copy after you think it changed things."* Since this was a backup version without the NULL values, what would I need to run in order to set the incorrect values to NULL where rain > 5 and rainRate > 10? On Monday, November 3, 2025 at 4:22:02 PM UTC-6 vince wrote: > I mean yes you still have what appears to bad data in it > > And yes, you will need to null out bad rainRate and possibly bad rain > (accumulation in an archive period) fields for those records, and > rebuild-daily for those days (there are options to say which dates) or you > can rebuild all dates. In my experiencing rebuilding just the dates you > altered is much faster, but either way works. > > And yes everything can be scripted if you are so inclined. > > On Monday, November 3, 2025 at 1:09:43 PM UTC-8 S Phillips wrote: > >> "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/760c5aca-b190-4bc7-9d12-49021d144eb4n%40googlegroups.com.
