Vince, here is a copy of the DB that is currently running w/o the previous NULL values that you had requested earlier in the day. I was unable to upload while at work. You can download from here: http://wx.emptymind.net/weewx_20251103_1915.sdb
On Monday, November 3, 2025 at 6:59:58 PM UTC-6 S Phillips wrote: > 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/6a2d4847-7b03-47eb-94f1-3b9bbe325d64n%40googlegroups.com.
