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/d5bf0db7-8fd7-4537-9201-33896de58c1cn%40googlegroups.com.
