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 On Saturday, 27 August 2022 at 20:35:21 UTC+10 peted...@gmail.com wrote: > 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 > > On Friday, 26 August 2022 at 03:40:09 UTC+1 gjr80 wrote: > >> 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 <https://www.epochconverter.com> >> 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 >> <http://weewx.com/docs/utilities.htm#Action_--calc-missing> 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 >> On Friday, 26 August 2022 at 05:50:12 UTC+10 peted...@gmail.com wrote: >> >>> 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? >>> >> -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/3532b162-f528-46ae-b9d9-8107b3bb8749n%40googlegroups.com.
