Imported rain data not being summarised.

[Alastair L]

Hi.  Help needed please.  I have imported several years of data from my old weather station into weewx.sdb. In particular, I imported the rain data into the [rain] field in the archive table and left the [rainRate] field blank. 

I then ran ‘sudo wee_database --drop-daily’ followed by ‘sudo wee_database --rebuild-daily’ and both ran ok without error.  The monthly and yearly summary reports generated for the imported temperature and wind data summarised ok but the precipitation summary data remains blank for some reason.

Any idea how I get the rain data summaries would be appreciated.  Thanks.

[Tom Keffer]

Hello,

If I understand correctly, you deleted, then regenerated all of the NOAA monthly and yearly reports. 

When you say "precipitation summary data remains blank," do you mean each line in one of the summaries? Or, do you mean the totals at the bottom of the page? And, is it every line? Or, only an occasional line?

It's worth looking at what is in the daily summary database. This should do it:

sqlite3 weewx.sdb

sqlite> select datetime(dateTime,'unixepoch','localtime'), sum from archive_day_rain;

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(sum) from archive_day_rain group by strftime("%Y",dateTime,'unixepoch','localtime');

The first select statement will give the rain for each day. It may be quite voluminous, depending on how big your database is.

The second will give the total rain for each year.

See if they look reasonable, then we can take it from there.

-tk

--
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/1fdb99af-6e6b-4a72-ac15-5389c6f27eb0n%40googlegroups.com.

[Alastair]

Hi Tom, many thanks for your response, apologies it wasn’t too clear. 

Yes I deleted and regenerated the reports after adding my old WS data.  

For info, I started using weewx with my new WS on dateTime = 1588201200 / 29-Apr-20. Before that date is the imported data from my old WS.  Here is a screenshot showing some data either side of that date (select datetime(dateTime,'unixepoch','localtime'), sum from archive_day_rain;) 

And here is the result of the second query  (select datetime(min(dateTime),'unixepoch','localtime'), sum(sum) from archive_day_rain group by strftime("%Y",dateTime,'unixepoch','localtime')

So thankfully it looks like my imported rain data has been processed.

However, here's a screenshot of  an example annual  summary for 2014 with the precipitation summary blank.  (Note that the wind and temperature summary is summarised)

Any thoughts would be very welcome.

Thanks again.

Alastair

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBf39Zc3xcpgMnRxodFyHB%3D7hTikPvxC4XibinTomR2KA%40mail.gmail.com.

[Tom Keffer]

Whether or not something appears in the yearly summaries depends on 'count', not 'sum'. Try this query for 2014

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(count), sum(sum) from archive_day_rain where strftime("%Y",dateTime,'unixepoch','localtime')='2014' group by strftime("%Y-%m",dateTime,'unixepoch','localtime');

Please cut and paste, rather than use screen shots. They take less space, are easier to read, and can be searched.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAKvi5dS-QQxtDEbr-wqoBCjPAJDU7s0Y10d97EVWx3bBdXUfGw%40mail.gmail.com.

[Alastair]

Here's the data from the query you sent. 

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(count), sum(sum) from archive_day_rain where strftime("%Y",dateTime,'unixepoch','localtime')='2014' group by strftime("%Y-%m",dateTime,'unixepoch','localtime');

2014-01-01 00:00:00|2941|5.858267927
2014-02-01 00:00:00|2685|10.110236633
2014-03-01 00:00:00|7364|24.838583605
2014-04-01 00:00:00|2974|0.0
2014-05-01 00:00:00|2564|0.0
2014-06-01 00:00:00|2562|0.0
2014-07-01 00:00:00|2778|2.78740166
2014-08-01 00:00:00|2082|24.307087427
2014-09-01 00:00:00|2873|10.759842921
2014-10-01 00:00:00|2972|27.248032548
2014-11-01 00:00:00|2875|4.110236407
2014-12-01 00:00:00|2974|2.964567051

Thanks

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEAnFbwmjiFM2_iJa_20LgXnCUxfpXNejEWqR_QEnThFEQ%40mail.gmail.com.

[Tom Keffer]

That looks OK. I assume your site did not have rain for 3 months?

We will need to see the log.

Stop weewx

Delete all the NOAA files

Set debug=1 in weewx.conf

Restart weewxd

Post the log from startup through the first reporting cycle.

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAKvi5dS4%2BeqHLpgqW7UzaDRNOPp-gT4ObK0qOBzidwsS69enOQ%40mail.gmail.com.

[Alastair]

Hi.  Log attached.  Hope I got this right and it's what you need .  

Yes, I saw the 3 months missing data.  This weather station is in Scotland - it's pretty well guaranteed to rain at least every other day.  The missing data must have been during one of the many occasions where the old weather station was acting up : (

Thanks again.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zECio9tahrEeq2JY%3DEF-Nty%2B3AZtVRwQMi5u3WWxzwHTXQ%40mail.gmail.com.

[Tom Keffer]

That all looks normal. What is in the NOAA subdirectory? Still blank lines?

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAKvi5dQ-vs8jPoizoFpHq6cd%2BwQLrWOqc908GWgyauw5oyyGzA%40mail.gmail.com.

[gjr80]

Perhaps I have misread the log extract but there seems to be some seven odd years of data in the db yet only eight files were generated in the first report cycle. If the NOAA files were deleted and re-generated I would have expected to see some 97 odd files being produced. Are you sure the NOAA reports were indeed deleted?

Gary

[Tom Keffer]

Good point. Make sure you delete the files first (as per post a couple messages up this thread).

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/228d6907-945c-43dd-aa48-d3141ed04f13n%40googlegroups.com.

[Alastair]

My lack of knowledge on weewx will no doubt become apparent here - apologies in advance, the Pi / weewx environment is all pretty new to me.  

I’m as sure as  I can be that I followed the procedure you gave me to generate the log file.  I'm taking it that the NOAA files are the various ‘archive_day_(etc)’ data tables within weewx.db?

To generate the missing summary data,  I have gone through the following steps again that I think is necessary and pated the results below.  As you will see below, I stopped the weewx service, ran the drop daily command and dropped the daily summaries, ran a query on the archive_day_rain table which shows the table didn't exist, restarted weewx service and finally re-ran a couple of queries to confirm the ‘archive_day_ rain table had been populated. 

Here’s a copy of the various commands as I went through them.   :

pi@raspberrypi:~ $ sudo /etc/init.d/weewx stop

[ ok ] Stopping weewx (via systemctl): weewx.service.

pi@raspberrypi:~ $ sudo wee_database --drop-daily

Using configuration file /etc/weewx/weewx.conf

Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

Proceeding will delete all your daily summaries from database 'weewx.sdb'

Are you sure you want to proceed (y/n)? y

Dropping daily summary tables from 'weewx.sdb' ...

Daily summary tables dropped from database 'weewx.sdb' in 2.99 seconds

pi@raspberrypi:~ $ cd /var/lib/weewx/

pi@raspberrypi:/var/lib/weewx $ sqlite3 weewx.sdb

SQLite version 3.27.2 2019-02-25 16:06:06

Enter ".help" for usage hints.

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(count), sum(sum) from archive_day_rain where strftime("%Y",dateTime,'unixepoch','localtime')='2014' group by strftime("%Y-%m",dateTime,'unixepoch','localtime');

Error: no such table: archive_day_rain

sqlite>

pi@raspberrypi:/var/lib/weewx $ sudo /etc/init.d/weewx start

[ ok ] Starting weewx (via systemctl): weewx.service.

pi@raspberrypi:/var/lib/weewx $ sqlite3 weewx.sdb

SQLite version 3.27.2 2019-02-25 16:06:06

Enter ".help" for usage hints.

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(count), sum(sum) from archive_day_rain where strftime("%Y",dateTime,'unixepoch','localtime')='2014' group by strftime("%Y-%m",dateTime,'unixepoch','localtime');

2014-01-01 00:00:00|2941|5.858267927

2014-02-01 00:00:00|2685|10.110236633

2014-03-01 00:00:00|7364|24.838583605

2014-04-01 00:00:00|2974|0.0

2014-05-01 00:00:00|2564|0.0

2014-06-01 00:00:00|2562|0.0

2014-07-01 00:00:00|2778|2.78740166

2014-08-01 00:00:00|2082|24.307087427

2014-09-01 00:00:00|2873|10.759842921

2014-10-01 00:00:00|2972|27.248032548

2014-11-01 00:00:00|2875|4.110236407

2014-12-01 00:00:00|2974|2.964567051

sqlite> select datetime(min(dateTime),'unixepoch','localtime'), sum(sum) from archive_day_rain group by strftime("%Y",dateTime,'unixepoch','localtime');
2012-11-02 00:00:00|16.606299895
2013-01-01 00:00:00|134.173233399
2014-01-01 00:00:00|112.984256179
2015-01-01 00:00:00|14.078740731
2016-02-27 00:00:00|16.759843256
2017-01-07 00:00:00|23.19685154
2018-06-18 00:00:00|12.874016437
2019-01-03 00:00:00|20.149607156
2020-01-01 00:00:00|37.0157479471
2021-01-01 00:00:00|12.8897637009

As previously, the annual summary  table precipitation data remains blank.   So it looks to me that weewx is  populating the archive_day_etc tables, just not showing the data in the annual summary.  If you can spot where I am going wrong here, I would much appreciate it.

Again, thanks for looking at this.

Alastair  

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zED3eadhLJ9u9Zm1OUgOiORWkdo9OnWA%2BJcg1newb0e7iA%40mail.gmail.com.

[gjr80]

Nothing to do with the database, you need to delete all of the NOAA format reports generated by WeeWX. Have a look in /var/www/html/weewx, I does it contain a directory named NOAA? If so have a look at the contents, should be a lot of .txt files. If so delete them all, this will force WeeWX to regenerate them on the next report cycle. The only NOAA format files that are generated each report cycle is the current month and current year files. Hence if you change historical data you need to delete them all to force them all to be regenerated.

Gary

[Alastair]

Ah, nice one, that hit the nail on the head!  Many thanks for both of you for your assistance (and patience), much appreciated.

Alastair

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/6b603bf8-a013-48f0-93ce-eab957ef2abbn%40googlegroups.com.