Correct Old and New Rain Records

[S Phillips]

Hello all, I am needing some advice on how to fix an issue that I have been having since 2016 related to my rain records.  Seems that lack of physical maintenance related to leaves and the rain collector has caused this problem with erroneous data being captured by my Vantage Pro and WeeWX. Combine that with lazyness and you get where I am now almost a decade later.  

On the records page for the Belchertown skin, I have bad data for the following areas: "Highest Daily Rainfall", "Highest Daily Rain Rate", "Month with Highest Total Rainfall" and "Total Rainfall for".  

I admit that I caused this issue in the past as I attempted to clean the data from the database. I later have learned that the method that I previously used was incorrect. I would like to keep my good historical data but I would like to get my values back to reading correctly.  Included is a screenshot of my records page which shows the culprit being in 2016 (for now).  Any assistance in correcting my messup would be much appreciated. 

(PS: My Pi that ran my previous WeeWX install died in Dec 2024 and due to health issues, I just got around to loading it on a VM so I have no new data until mid-October 2025).

[Tom Keffer]

WeeWX actually has two kinds of databases: an "archive" database, and "daily summaries." The archive database is definitive: you can always recreate the daily summaries using the archive database.

So the question is: where does the bad data reside? In the archive database, or in the daily summaries? The easiest way to test is to just recreate the summaries. If that fixes the problem, you're done. If not, you'll have to go on a hunt for the bad data in the archive database.

To recreate the summaries:

weectl database drop-daily

weectl database rebuild-daily

If that doesn't fix it, then you'll have to find the bad data in the archive database. See this article: Cleaning up old bad data.

-tk

--
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 weewx-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/d251d6fb-8dfa-49b5-bb46-a96474ed7eban%40googlegroups.com.

[vince]

Suggest you look at each yearly report to see which month(s) or year(s) you think you have issues to clean up.  We can't guess what normal vs. abnormal might be for your site, as everybody has different measures of 'normal' for rain.

Poke around your NOAA directory if you're comfortable with the command line, but note that Belchertown has its own NOAA tree under the hood of the html files it generates.  If you are running other skins like the default Seasons or Standard skins, they would go into a different NOAA directory probably.

Or just get there through the web gui.  Look year by year.  Note which date ranges look like they're wrong. We can give you more specific guidance when we know what dates we might need to fiddle with.

[S Phillips]

Sorry for the delay in response to the suggestions, been quite busy as of late.  

I performed the drop daily and rebuild daily as recommended, but unfortunately that did not fix it. I gathered the NOAA reports from the WeeWX to get an idea as to where the bad data might reside.  I also gathered the actual readings which were observed from the nearest official NOAA station and use that data to figure out which is true or not. 

One of the issues that I am unable to grab from NOAA is the rain rate as it does not appear to be collected.  The "highest daily rain rate" from my WeeWX installation/DB is "84.62 in/hr" which is obviously incorrect.  I ran the following SQL query to see how many lines were over 10 (which I assume is 10 in/hr) which resulted in 29 lines of which 15 of those lines were between 10 and 29.  The rest were 40 or over so I assume that those are all bad values.  My question regarding that is what is an average rain rate that is observed and what is "extreme"?  

[vince]

We can't answer what is extreme for your location. What is your location ?

For me anything over 4 is extreme...looking back at 16+ years of data:

# echo "select datetime(dateTime,'unixepoch','localtime'),dateTime,rainRate from archive where rainRate > 4;" | sqlite3 vp2.sdb
2010-06-09 17:00:00|1276128000|5.14
2010-06-09 17:05:00|1276128300|5.76
2011-10-11 17:00:00|1318377600|7.02
2012-04-25 17:45:00|1335401100|6.94
2012-06-07 13:40:00|1339101600|4.57
2013-09-28 16:45:00|1380411900|4.84
2013-09-30 15:20:00|1380579600|5.49
2014-01-11 04:25:00|1389443100|4.68
2014-01-11 04:30:00|1389443400|4.68
2014-05-03 22:45:00|1399182300|4.97
2015-01-18 11:05:00|1421607900|5.2799999946144
2015-06-02 12:40:00|1433274000|7.99999999184
2015-10-10 14:30:00|1444512600|5.4299999944614
2015-10-31 11:30:00|1446316200|4.8399999950632
2016-08-07 22:00:00|1470632400|4.9199999949816
2017-02-03 09:20:00|1486142400|5.0099999948898
2017-04-22 18:35:00|1492911300|4.3599999955528
2018-04-07 08:40:00|1523115600|4.09
2019-09-07 19:45:00|1567910700|5.65
2024-01-09 00:05:00|1704787500|5.76

Which lines up with the summary table:

 # echo "select datetime(dateTime,'unixepoch','localtime'),dateTime,max from archive_day_rainRate where max > 4;" | sqlite3 vp2.sdb
2010-06-09 00:00:00|1276066800|5.76
2011-10-11 00:00:00|1318316400|7.02
2012-04-25 00:00:00|1335337200|6.94
2012-06-07 00:00:00|1339052400|4.57
2013-09-28 00:00:00|1380351600|4.84
2013-09-30 00:00:00|1380524400|5.49
2014-01-11 00:00:00|1389427200|4.68
2014-05-03 00:00:00|1399100400|4.97
2015-01-18 00:00:00|1421568000|5.2799999946144
2015-06-02 00:00:00|1433228400|7.99999999184
2015-10-10 00:00:00|1444460400|5.4299999944614
2015-10-31 00:00:00|1446274800|4.8399999950632
2016-08-07 00:00:00|1470553200|4.9199999949816
2017-02-03 00:00:00|1486108800|5.0099999948898
2017-04-22 00:00:00|1492844400|4.3599999955528
2018-04-07 00:00:00|1523084400|4.09
2019-09-07 00:00:00|1567839600|5.65
2024-01-09 00:00:00|1704787200|5.76

Interestingly for me, most of the big rainRate(s) do not equate to high rain totals.   The squall comes in, dumps a bucket of rain in a heartbeat, then is gone.

[vince]

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.

[S Phillips]

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.

[vince]

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.

[Jeff A. D.]

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.)

[Mark Jenks]

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.

[vince]

I can see either answer in this case.  Agree with Mark about NULL vs. zero.    Tom's words in the wiki recommend NULL (link).

[S Phillips]

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.sdb

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?

 

[vince]

rainRate not rainrate in your image FWIW

I would redo your queries off your working copy after you think it changed things.  Can’t help you with DB Browser. I use sqlite3 in a terminal from the command line.

If you can make the db available online someplace I can take a look…

[Tom Keffer]

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.

--
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 weewx-user+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/3f27c2db-4d1b-4fe3-86b1-f34c9420fe20n%40googlegroups.com.

[Jeff A. D.]

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. 

[vince]

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....

[S Phillips]

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

[vince]

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…

[S Phillips]

"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? 

[vince]

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.

[S Phillips]

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? 

[S Phillips]

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

[vince]

I'm sorry this will be a long reply.  Your db is very odd and has a lot of very unusual content.

You have all kinds of varying 'interval' values before 2019-03-15 so I can't guess what a reasonable amount of rain in an interval period might be before that date.   Typically an interval period is very consistent with well-known values (see https://www.weewx.com/docs/5.2/hardware/vantage/?h=interval#vantage_archive_interval for details).  Yours is all over the map and before that date it's pretty messy. The last odd intervals appear on 2019-03-15.

To illustrate, this command looks at anything after 2019-03-01 as an example and reports the archive period interval and human-friendly yyyy-mm-dd date to show you what I'm seeing.

echo "select interval,datetime(dateTime,'unixepoch','localtime') from archive where interval!=5 and datetime(dateTime,'unixepoch','localtime')>'2019-03-01;" | sqlite3 weewx_20251103_1915.sdb | awk '{print $1}' | uniq

As an example - 2016-07-24 has 38 different archive intervals (wow) ranging from 2 to 60.  That's so odd I don't know what to suggest there.

So you have a few choices in this very unusual case:

• 1. delete all data before the dateTime when the db intervals looked odd, meaning any data from 2016-03-01 to 2019-0315 would be deleted
• 2. or keep it all and just fix up rain and rainRate for anyplace there's stuff in there that you think needs fixing up
• 3. or totally null out rain and/or rainRate for all records from 2016-03-01 to 2019-0315 when the intervals were odd
• 4. or just clean up rainRate and see if that's good enough for you

Given how wildly unusual your db is, I really can't suggest which option above to do.  Sorry.

That said - assuming you want to do option-2 above and keep all your data and fix up only the overly high rain or rainRate data, here's how you can do it...

# 1. Save a list of dates have high rainRate(s) or  high rain for an archive period - you'll need this later

echo "select datetime(dateTime,'unixepoch','localtime') from archive where rainRate>10;" | sqlite3 weewx_20251103_1915.sdb | awk '{print $1}' | sort |uniq

echo "select datetime(dateTime,'unixepoch','localtime') from archive where rain>1;" | sqlite3 weewx_20251103_1915.sdb | awk '{print $1}' | sort |uniq

Note - I picked 10" for rainRate as you asked, and 1" for rain because it seemed more reasonable based on what is in your db.  You might even lower the rainRate threshold to a lower number.

# 2. To clear the archive up you basically change "select something from archive where...." to "update archive set something=....." in your command

update archive set rain=NULL where rain>1

update archive set rainRate=NULL where rainRate>10

Unfortunately I didn't have luck updating both at once, but running two commands is very quick so I didn't dig in google for more sqlite3 magic syntax to 'or' (not 'and') the two things together.

# 3. then to fix up the archive_day_rain and archive_day_rainRate summary tables you would rebuild-daily for just the dates you saved above...

      weectl database rebuild-daily
            [[--date=YYYY-mm-dd] | [--from=YYYY-mm-dd] [--to=YYYY-mm-dd]]
            [--config=FILENAME] [--binding=BINDING-NAME]
            [--dry-run] [-y]

Given the odd intervals for many of the affected dates, I personally would manually rebuild-daily for each of the dates with data you need to clean up.  I come up with the following list of days with rainRate > 10 "or" rain > 1 for whatever archive period you had set then.  Given the odd and very varying archive periods, you might want to look day-by-day at each date before 2019-03-16 and consider if the totals there are ok for you.  I really don't know what rebuild-daily is going to do with such a varying interval even within one calendar day.

(dates with odd intervals)

2016-07-24
2016-08-12
2016-08-28
2017-09-15
2017-09-18
2017-09-22
2017-09-23
2017-09-26
2017-09-27

2018-03-17

(dates with reasonable consistent intervals)
2019-10-11
2020-02-17
2020-05-17
2020-07-11
2021-06-24
2021-08-30
2022-02-22
2022-07-08
2023-08-10

# 4. you'll want to delete the NOAA files for any months and years with data you altered.  Belchertown has its own NOAA tables directory in 'addition' to the one weewx typically generates if you have the Standard or Seasons skin enabled.  Be sure to delete those files in both places.   Weewx will regenerate its when it starts up.  I typically let it start up then just copy the Weewx NOAA files into the Belchertown NOAA tree to make it start up quicker.

Sorry this was so long - but your db is so unusual I needed to provide too much info probably....

[S Phillips]

Thank you for looking at the raw data and coming up with some solutions to the issue.  Whereas I would like to retain as much data as possible, I also know that by doing so it will retain bad data which can affect things down the road. I am ok with taking a loss and removing data from 2016-03-01 to 2019-03-15 if that will fix the inconsistencies in the database. How might I go about doing that? 

[vince]

Let me fiddle a little with it and give it some thought.   Stay tuned.

(gotta step out for a few hours)

The 'rain' readings after that 2019-03-15 actually look ok in a quick glance but there are some bad rainRate(s) in there for some reason I can't explain.

echo "select datetime(dateTime,'unixepoch','localtime'),rainRate from archive where rainRate>5;" | sqlite3 weewx.sdb

2019-05-21 18:50:00|5.1
2019-10-11 14:55:00|82.29
2019-10-11 15:00:00|64.0
2019-10-24 14:35:00|6.0
2020-02-17 14:35:00|26.18
2020-05-02 19:30:00|9.44
2020-05-02 19:35:00|7.02  <= this one looks like your true max (its rain is ok)
2020-05-17 10:05:00|82.29
2020-05-26 12:35:00|5.1
2020-07-07 15:10:00|5.1
2020-07-11 12:45:00|14.77
2020-07-21 07:45:00|7.38
2020-07-21 13:55:00|8.11
2020-07-21 14:00:00|8.11
2020-08-15 15:45:00|6.47
2020-08-15 15:50:00|5.7
2021-03-11 00:15:00|5.05
2021-06-24 19:25:00|82.29
2021-06-26 13:15:00|6.47
2021-08-09 02:55:00|5.01
2021-08-12 12:50:00|6.62
2021-08-12 12:55:00|5.1
2021-08-30 15:20:00|15.57
2021-10-11 11:20:00|7.11
2021-10-11 11:25:00|7.48
2022-02-22 04:35:00|11.29
2022-06-17 02:55:00|5.54
2022-06-17 03:00:00|5.24
2022-07-08 06:05:00|82.29
2022-07-08 16:40:00|5.38
2022-08-02 07:35:00|5.54
2022-08-28 13:00:00|5.38
2022-09-19 01:30:00|5.65
2023-08-10 05:05:00|82.29
2023-08-10 05:10:00|57.6
2023-08-11 07:20:00|7.58
2024-03-14 05:10:00|6.86
2024-03-14 06:05:00|7.11
2024-07-16 00:20:00|5.7
2024-07-16 00:25:00|7.11
2024-07-16 00:45:00|5.19
2024-07-16 00:50:00|5.19
2024-07-30 13:00:00|6.47
2024-07-30 13:05:00|5.1