Missing rainRate data

[Peter Cooper-Davis]

I recently (25/08/2022) had to delete a 6 hour section of data from my database as the rain accumulation data contained in the LOOP packets from that period were incorrect. I refilled the database using the correct rain accumulation data from ARCHIVE packets over that 6 hours, and everything (in terms of the daily summaries etc.) now looks good. The only issue I have though is that there is no rainRate data in my database. Looking at a selection of records I can see rain accumulation, but no rainRate:

dateTime                    |rain          |rainRate
2022-08-25 06:15:00|1.018610|0.0
2022-08-25 06:20:00|0.978673|0.0
2022-08-25 06:25:00|1.367165|0.0
2022-08-25 06:30:00|1.710911|0.0
2022-08-25 06:35:00|1.588113|0.0
2022-08-25 06:40:00|1.858461|0.0
2022-08-25 06:45:00|1.654587|0.0
2022-08-25 06:50:00|1.475450|0.0
2022-08-25 06:55:00|1.498110|0.0

I have tried using wee_database --calc-missing as I assumed rainRate was a derived value, but this doesn't seem to have made any difference. Can anyone advise how to fill the missing rainRate fields?

[gjr80]

wee_database --calc-missing will only populate missing derived obs provided (1) the missing derived obs field contains no data and (2) the pre-requisites for the calculation exist. In your case you are failing on the first test - your rainRate fields contain the value 0. You can try setting the rainRate field to null in each affected record. Assuming you are using a SQLite database this is most reliably done using the sqlite3 utility. To do this:

1. using something like Epoch Converter to determine the epoch timestamp for the first and last archive records in the block of records you wish to update, let's refer to these values as start_timestamp and end_timestamp

2. install the sqlite3 utility if not already installed:

    $ sudo apt install sqlite3

3. stop WeeWX and make a backup of your WeeWX database, unless you have changed it this will be /home/weewx/archive/weewx.sdb or /var/lib/weewx/weewx.sdb depending on your WeeWX install type 

4. set the rainRate field for the records concerned to null:

    $ sqlite3 /home/weewx/archive/weewx.sdb

    > UPDATE archive SET rainRate=NULL WHERE dateTime>=start_timestamp AND dateTime<=end_timestamp;

    replacing start_timestamp and end_timestamp with the actual epoch timestamps. You can check the data has been cleared with the following query:

    > SELECT dateTime,datetime(dateTime, 'unixepoch','localtime'),rain,rainRate FROM archive WHERE dateTime>=start_timestamp AND dateTime<=end_timestamp;

5. once happy the rainRate values have been nulled exit sqlite3 with .q

6. run wee_database --calc-missing to calculate the missing rainRate data, use the --date or --from and --to to limit the span of records that wee_database will operate on.

7. check the if rainRate has been correctly calculated:

    $ sqlite3 /home/weewx/archive/weewx.sdb

    > SELECT dateTime,datetime(dateTime, 'unixepoch','localtime'),rain,rainRate FROM archive WHERE dateTime>=start_timestamp AND dateTime<=end_timestamp;

    > .q

If you are using MySQL/MariaDB the approach is the same, but you would use mysql in lieu of sqlite3.

Gary

[Peter Cooper-Davis]

Hi Gary,

Thanks for the quick response. Your answer makes complete sense, but unfortunately it doesn't seem to work. Even after I have set the rainRate fields to NULL,  wee_database --calc-missing just seems to refill the rainRate with 0.0.

I've checked that I have definitely set the rain rate fields to NULL using the command you suggested above:

> SELECT dateTime,datetime(dateTime, 'unixepoch','localtime'),rain,rainRate FROM archive WHERE dateTime>=1661385600;

1661405100|2022-08-25 06:25:00|0.016904|
1661405400|2022-08-25 06:30:00|0.065687|
1661405700|2022-08-25 06:35:00|0.037562|
1661406000|2022-08-25 06:40:00|0.185201|
1661406300|2022-08-25 06:45:00|0.178290|
1661406600|2022-08-25 06:50:00|0.038078|
1661406900|2022-08-25 06:55:00|0.075650|
1661407200|2022-08-25 07:00:00|0.376993|
1661407500|2022-08-25 07:05:00|1.389899|
1661407800|2022-08-25 07:10:00|1.569504|
1661408100|2022-08-25 07:15:00|1.018610|

But after running wee_database --calc-missing --from=2022-08-25 the database looks like this

> SELECT dateTime,datetime(dateTime, 'unixepoch','localtime'),rain,rainRate FROM archive WHERE dateTime>=1661385600;

1661405100|2022-08-25 06:25:00|0.016904|0.0
1661405400|2022-08-25 06:30:00|0.065687|0.0
1661405700|2022-08-25 06:35:00|0.037562|0.0
1661406000|2022-08-25 06:40:00|0.185201|0.0
1661406300|2022-08-25 06:45:00|0.178290|0.0
1661406600|2022-08-25 06:50:00|0.038078|0.0
1661406900|2022-08-25 06:55:00|0.075650|0.0
1661407200|2022-08-25 07:00:00|0.376993|0.0
1661407500|2022-08-25 07:05:00|1.389899|0.0
1661407800|2022-08-25 07:10:00|1.569504|0.0
1661408100|2022-08-25 07:15:00|1.018610|0.0

Is there something obvious I am missing?

Pete

[gjr80]

No you've not missed anything, but I have. I thought that rainRate was calculated by wee_database --calc-missing but on looking at the rain rate calculation code I can see that it will never calculate rainRate under wee_database --calc-missing. Let me look at this some more.

Gary