[weewx-user] Re: Missing rainRate data

2022-08-29 Thread 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
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  
>> 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
>> 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 thou

[weewx-user] Re: Missing rainRate data

2022-08-27 Thread 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

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

[weewx-user] Re: Missing rainRate data

2022-08-25 Thread 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
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/150e83c7-73a7-4756-a2df-6bdd753dce38n%40googlegroups.com.