Delete all rain data from specific time period

[Big Hiller]

Hi,

I'm no good at SQL or pretty much anything in Linux other than getting my WeeWX instance running.

It's been running great for almost a year now and I have a little issue I was hoping to get a little help on. 

My wife has been putting the sprinkler into a new place and I hadn't noticed until a little while back but when she waters the lawn it's been going into the rain sensor and has been getting measured as rain fall. Clearly this is not good and luckily we have had no rain at all here in Vancouver for the last few months so I feel it's safe to simple null out any data for that sensor in the specific range.

That leads me to the question:
How (which command) would I delete all the rainfall data from June 14, 2021 to August 1, 2021?

Probably really simple for some of you :)

Thanks in advance!

[Tom Keffer]

1. What time zone are you in?

2. What database are you using?

--
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 on the web visit https://groups.google.com/d/msgid/weewx-user/764c76c8-9162-497e-ad08-01cbb8d65f7an%40googlegroups.com.

[Big Hiller]

1. Pacific (PST)

2. MySQL, I believe.

[Tom Keffer]

We need to know (for sure) what database you are using. Look in your weewx.conf file, for the section that looks like:

[DataBindings]

    [[wx_binding]]
        # The database must match one of the sections in [Databases].
        # This is likely to be the only option you would want to change.
        database = archive_sqlite

We need the line for the option "database". It will say "archive_mysql" if you are using MySQL, "archive_sqlite" otherwise.

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/816feb5b-9c27-4dc9-9e07-b14e0fa220c7n%40googlegroups.com.

[Big Hiller]

Phew! Good thing you asked, it says "database = archive_sqlite"

I did say I am a n00b ;)

[Tom Keffer]

1. Stop weewx.

2. Install the tool sqlite3, if it has not been installed already:

sudo apt install sqlite3

3. Calculate the starting and ending dates in unix epoch time. These are the dates that you want to zero out. The tool https://www.epochconverter.com/ is useful for this.

14 June 2021 0000 = 1623654000

1 August 2021 0000 = 1627801200

4. Find your sqlite database file. If you did a package install, it will be in /var/lib/weewx/weewx.sdb. If you did a setup.py install, it will be in /home/weewx/archive/weewx.sdb. I'm assuming the former.

5. Make a backup of the database file:

sudo cp /var/lib/weewx/weewx.sdb /var/lib/weewx/weewx.sdb.backup

6. Use the tool sqlite3 to see what's in the database. This select statement will show the epoch time, date, and rain for the period 14 June until 1 August. Make sure you include the semicolon at the end. It will print out a lot of rows. Look them over.

sudo sqlite3 /var/lib/weewx/weewx.sdb

sqlite> select datetime, datetime(dateTime, 'unixepoch','localtime'), rain from archive where dateTime > 1623654000 and dateTime <= 1627801200;

7. If it meets your expectations, zero out the rain fields for that period:

sqlite> update archive set rain=0.0 where dateTime > 1623654000 and dateTime <= 1627801200;

sqlite> .quit

8. Rebuild the daily summaries for the affected period.

sudo wee_database --rebuild-daily --from=2021-06-14 --to=2021-08-01

9. Restart weewx.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/762f209a-cdcf-4d0e-9912-f451c5db205an%40googlegroups.com.

[Big Hiller]

LIKE. A. CHARM!

You, sir, are a gentleman and a scholar!

Thank you so much for the clear and concise instructions. I'm sure I'll refer to this thread in the future should I ever get more erroneous data.

Again, thanks!!

[gjr80]

Probably not used/referred to as often as rain, but you may want to give rainRate the same treatment. If your station does not emit rainRate (most don’t) chances are WeeWX calculates rainRate for you. In such cases a period of bogus rainfall will cause a period of bogus rainRate. The other thing regards rainRate is that due to WeeWX using a 15 minute (default value) sliding window to calculate rainRate you need to extend your period of concern by at least 15 minutes (ie add 900 to the higher of the two timestamp values used in the rain query).

Gary

[p q]

You might also want to check out a GUI tool that can open/read/edit a SQLite database such as https://github.com/sqlitebrowser/sqlitebrowser/wiki 

I personally used it on Windows. I backup and copy the db to a windows machine, carefully manually remove bad data, and put it back. 

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/c83481be-fc1f-4212-afd3-6bc0d5da32c7n%40googlegroups.com.

--

Peter Quinn
(415)794-2264