Wrong rain in daily archive but ok in archive

[Roberto Verrelli]

Hi,

i have the following issue.

I imported 2 years of data in weewx database but i noticed some issues on daily archive.

The most important is with the rain.

Taking data from archive table, i perform the following query on sql db for a specific day in order to have the total amount of rain for that specific day.

The value i obtain is the correct i expect ,for that day i had 1.13385 inch of rain.

select sum(rain) from archive where dateTime > 1601157600 and dateTime <= 1601244000 and rain != 0

In my NOAA report, and on daily archive for that day i have completely different values( 0.645669 inches).

In particular for the same day as before but this time from the archive_daily_rain i have the following output

sqlite> select * from archive_day_rain where dateTime=1601157600;
dateTime|min|mintime|max|maxtime|sum|count|wsum|sumtime
1601157600|0.0|1601158071|0.165354330708661|1601218891|0.645669|197|920.551181102362|4782

in the sum field there is that  0.645669   inches that is the value i have in NOAA report that is wrong.

Why is weewx doing this?i can i fix this for all day i had rain?

I tried:

wee_database --update

wee_database --reweight

dropped the daily record and regenerated

No one of this action fixed this issue.

Thanks in advance.

Best regards.

Roberto

[gjr80]

Hi,

Just a couple of points on terminology so we are on the same sheet of music. In a default install your WeeWX database has a table named archive, this is commonly known as 'the archive'. There are numerous other tables in the database whose name are in the format archive_day_xxxxx, these tables are known as 'the daily summaries'. The daily summaries are essentially an optimisation of data derived from the archive so that certain aggregates can be calculated much more quickly.

If you are confident the data in your archive is correct then all you need do to correct the daily summaries is to drop then rebuild the daily summaries. Do this by first stopping WeeWX then running wee_database with the --drop-daily action. Once you have dropped the daily summaries you can rebuild them from the data in your archive by again running wee_database but this time with the --rebuild-daily action. Make sure you make a backup of your database first.

If you still find your data is incorrect/inconsistent post back here with exact commands entered and the exact responses along with details of the discrepancies.

Once you have your database sorted you can fix the NOAA format report problem by deleting all of the generated NOAA format reports from your WeeWX machine to force WeeWX to regenerate them all on the next report cycle. Where these files are located depends on your WeeWX install, for a setup.py install they will likely be in /home/weewx/public_html/NOAA and for a package install /var/www/html/weewx/NOAA.

Gary

[Roberto Verrelli]

Hi gary,

As i reported,I already did every step you suggested,data on archive_daily_rain is still wrong....

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/pQohXWFsEjk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/fab0df47-320b-478c-bed9-717ae22d2f68n%40googlegroups.com.

[gjr80]

I'm sorry I am a bit confused. You just said 'as reported I already did every step you suggested' but your original post made no mention of the steps I mentioned. It's not clear to me what you have/have not done.

Irrespective, can you please stop WeeWX, execute the wee_database --drop-daily and --rebuild-daily commands. Please capture the console output showing the exact commands entered and the exact output. No images please, copy and paste as text. Then run your SQLite queries again and again post the exact commands entered and the response.

Gary

[Roberto Verrelli]

Hi Gary,

i performed the step you reported,dropping daily and rebuilding them.

I have still the issue,below the queries after steps you reported.

Thanks in advance.

sqlite> select sum(rain) from archive where dateTime > 1601157600 and dateTime <= 1601244000 and rain != 0;
sum(rain)
1.13385826771654

sqlite> select * from archive_day_rain where dateTime=1601157600;
dateTime|min|mintime|max|maxtime|sum|count|wsum|sumtime

1601157600|0.0|1601158071|0.165354330708661|1601218891|0.645669291338583|197|920.551181102362|47820

[gjr80]

OK, if there is a discrepancy between the daily summaries and the archive the solution is to drop and rebuild the daily summaries. If that approach does not work we need to track down why. The first step is to look at the output of the wee_database --drop-daily  and wee_database --rebuild-daily commands. As requested previously, could you please execute these commands again and post the console output here showing both the exact commands entered and the exact output. 

Gary

[Roberto Verrelli]

Hi Gary,

sorry for late reply,below the output requested

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 3.25 seconds

pi@raspberrypi:~ $ sudo wee_database --rebuild-daily
Using configuration file /etc/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_sqlite'
All daily summaries will be rebuilt.
Proceed (y/n)? y
Rebuilding daily summaries in database 'weewx.sdb' ...
Records processed: 1000; time: 2020-09-22 14:36:Records processed: 2000; time: 2020-09-25 21:03:Records processed: 3000; time: 2020-09-30 10:42:Records processed: 4000; time: 2020-10-04 21:06:Records processed: 5000; time: 2020-10-08 21:49:Records processed: 6000; time: 2020-10-13 10:13:Records processed: 7000; time: 2020-10-17 05:03:Records processed: 8000; time: 2020-10-21 08:39:Records processed: 9000; time: 2020-10-25 18:34:Records processed: 10000; time: 2020-10-30 14:05Records processed: 11000; time: 2020-11-03 12:49Records processed: 12000; time: 2020-11-07 19:50Records processed: 13000; time: 2020-11-12 14:04Records processed: 14000; time: 2020-11-16 19:42Records processed: 15000; time: 2020-11-21 08:29Records processed: 16000; time: 2020-11-25 14:01Records processed: 17000; time: 2020-11-29 08:37Records processed: 18000; time: 2020-12-03 15:01Records processed: 19000; time: 2020-12-08 03:51Records processed: 20000; time: 2020-12-12 13:44Records processed: 21000; time: 2020-12-16 22:08Records processed: 22000; time: 2020-12-20 15:07Records processed: 23000; time: 2020-12-24 12:40Records processed: 24000; time: 2020-12-28 06:50Records processed: 25000; time: 2021-01-01 02:16Records processed: 26000; time: 2021-01-05 00:29Records processed: 27000; time: 2021-01-08 22:31Records processed: 28000; time: 2021-01-12 18:39Records processed: 29000; time: 2021-01-16 18:37Records processed: 30000; time: 2021-01-20 07:18Records processed: 31000; time: 2021-01-23 13:24Records processed: 32000; time: 2021-01-26 21:10Records processed: 33000; time: 2021-01-31 04:52Records processed: 34000; time: 2021-02-04 09:17Records processed: 35000; time: 2021-02-08 07:48Records processed: 36000; time: 2021-02-12 14:48Records processed: 37000; time: 2021-02-15 13:49Records processed: 38000; time: 2021-02-19 03:57Records processed: 39000; time: 2021-02-22 17:48Records processed: 40000; time: 2021-02-26 09:37Records processed: 41000; time: 2021-03-01 19:11Records processed: 42000; time: 2021-03-05 13:45Records processed: 43000; time: 2021-03-09 00:23Records processed: 44000; time: 2021-03-12 07:20Records processed: 45000; time: 2021-03-15 21:30Records processed: 46000; time: 2021-03-19 06:32Records processed: 47000; time: 2021-03-22 23:58Records processed: 48000; time: 2021-03-26 08:19Records processed: 49000; time: 2021-03-29 15:55Records processed: 50000; time: 2021-04-02 00:38Records processed: 51000; time: 2021-04-04 21:41Records processed: 52000; time: 2021-04-08 09:34Records processed: 53000; time: 2021-04-11 20:15Records processed: 54000; time: 2021-04-15 17:33Records processed: 55000; time: 2021-04-19 02:08Records processed: 56000; time: 2021-04-22 05:25Records processed: 57000; time: 2021-04-26 01:08Records processed: 58000; time: 2021-04-29 22:01Records processed: 59000; time: 2021-05-03 11:39Records processed: 60000; time: 2021-05-06 23:48Records processed: 61000; time: 2021-05-10 12:24Records processed: 62000; time: 2021-05-13 23:24Records processed: 63000; time: 2021-05-17 19:52Records processed: 64000; time: 2021-05-21 08:22Records processed: 65000; time: 2021-05-25 10:34Records processed: 66000; time: 2021-05-28 23:17Records processed: 67000; time: 2021-06-02 08:09Records processed: 68000; time: 2021-06-06 03:27Records processed: 69000; time: 2021-06-10 00:53Records processed: 70000; time: 2021-06-14 02:34Records processed: 71000; time: 2021-06-18 07:39Records processed: 72000; time: 2021-06-22 20:11Records processed: 73000; time: 2021-06-27 12:29Records processed: 74000; time: 2021-07-02 03:15Records processed: 75000; time: 2021-07-06 12:26Records processed: 76000; time: 2021-07-11 05:39Records processed: 77000; time: 2021-07-14 23:37Records processed: 78000; time: 2021-07-19 02:45Records processed: 79000; time: 2021-07-23 05:42Records processed: 80000; time: 2021-07-27 03:58Records processed: 81000; time: 2021-07-30 14:07Records processed: 82000; time: 2021-08-03 00:34Records processed: 83000; time: 2021-08-07 09:55Records processed: 84000; time: 2021-08-11 12:32Records processed: 85000; time: 2021-08-15 16:04Records processed: 86000; time: 2021-08-19 09:11Records processed: 87000; time: 2021-08-23 08:19Records processed: 88000; time: 2021-08-25 19:11Records processed: 89000; time: 2021-09-01 11:44Records processed: 90000; time: 2021-09-05 06:39Records processed: 91000; time: 2021-09-08 16:02Records processed: 92000; time: 2021-09-12 07:46Records processed: 93000; time: 2021-09-16 06:32Records processed: 94000; time: 2021-09-20 16:55Records processed: 95000; time: 2021-09-23 22:19Records processed: 96000; time: 2021-09-28 02:15Records processed: 97000; time: 2021-10-02 04:30Records processed: 98000; time: 2021-10-06 07:38Records processed: 99000; time: 2021-10-10 07:12Records processed: 100000; time: 2021-10-13 16:4Records processed: 101000; time: 2021-10-17 04:3Records processed: 102000; time: 2021-10-21 07:4Records processed: 103000; time: 2021-10-24 21:0Records processed: 104000; time: 2021-10-29 23:5Records processed: 105000; time: 2021-11-04 12:1Records processed: 106000; time: 2021-11-09 06:2Records processed: 107000; time: 2021-11-13 09:3Records processed: 108000; time: 2021-11-17 16:0Records processed: 109000; time: 2021-11-22 19:3Records processed: 110000; time: 2021-11-27 18:2Records processed: 111000; time: 2021-12-02 02:4Records processed: 112000; time: 2021-12-07 00:1Records processed: 113000; time: 2021-12-11 01:2Records processed: 114000; time: 2021-12-15 21:4Records processed: 115000; time: 2021-12-20 14:1Records processed: 116000; time: 2021-12-24 21:1Records processed: 117000; time: 2021-12-28 13:0Records processed: 118000; time: 2022-01-03 10:3Records processed: 119000; time: 2022-01-07 00:0Records processed: 120000; time: 2022-01-11 00:0Records processed: 121000; time: 2022-01-18 18:0Records processed: 122000; time: 2022-01-23 00:2Records processed: 123000; time: 2022-01-27 14:0Records processed: 124000; time: 2022-02-01 00:0Records processed: 125000; time: 2022-02-05 07:5Records processed: 126000; time: 2022-02-08 22:4Records processed: 127000; time: 2022-02-13 11:5Records processed: 128000; time: 2022-02-22 23:0Records processed: 129000; time: 2022-03-02 01:2Records processed: 130000; time: 2022-03-06 17:1Records processed: 131000; time: 2022-03-11 18:0Records processed: 132000; time: 2022-03-16 06:5Records processed: 133000; time: 2022-03-20 07:1Records processed: 134000; time: 2022-03-24 10:2Records processed: 135000; time: 2022-03-28 06:5Records processed: 136000; time: 2022-04-01 04:0Records processed: 137000; time: 2022-04-05 02:1Records processed: 138000; time: 2022-04-09 07:0Records processed: 139000; time: 2022-04-16 18:2Records processed: 140000; time: 2022-04-21 02:1Records processed: 141000; time: 2022-04-24 21:3Records processed: 142000; time: 2022-04-28 21:2Records processed: 143000; time: 2022-05-02 14:2Records processed: 144000; time: 2022-05-06 16:3Records processed: 145000; time: 2022-05-11 07:2Records processed: 146000; time: 2022-05-15 18:4Records processed: 147000; time: 2022-05-22 20:5Records processed: 148000; time: 2022-05-26 03:4Records processed: 149000; time: 2022-05-30 19:3Records processed: 150000; time: 2022-06-04 03:0Records processed: 151000; time: 2022-06-08 23:4Records processed: 152000; time: 2022-06-13 09:2Records processed: 153000; time: 2022-06-18 02:0Records processed: 154000; time: 2022-06-23 14:5Records processed: 155000; time: 2022-06-28 03:3Records processed: 156000; time: 2022-07-02 12:4Records processed: 157000; time: 2022-07-06 10:1Records processed: 158000; time: 2022-07-10 12:5Records processed: 159000; time: 2022-07-14 22:0Records processed: 160000; time: 2022-07-17 21:4Records processed: 161000; time: 2022-07-21 05:5Records processed: 162000; time: 2022-07-24 09:4Records processed: 163000; time: 2022-07-27 21:5Records processed: 164000; time: 2022-07-30 19:5Records processed: 165000; time: 2022-08-03 09:2Records processed: 166000; time: 2022-08-07 06:1Records processed: 167000; time: 2022-08-11 13:1Records processed: 168000; time: 2022-08-16 19:5Records processed: 169000; time: 2022-08-21 23:3Records processed: 170000; time: 2022-08-27 04:0Records processed: 171000; time: 2022-09-01 03:4Records processed: 172000; time: 2022-09-06 09:0Records processed: 173000; time: 2022-09-11 01:3Records processed: 174000; time: 2022-09-14 10:2Records processed: 175000; time: 2022-09-17 14:1Records processed: 176000; time: 2022-09-22 10:1Records processed: 177000; time: 2022-09-25 09:2Records processed: 178000; time: 2022-09-27 13:5Records processed: 179000; time: 2022-09-29 13:4Records processed: 180000; time: 2022-10-01 05:5Records processed: 181000; time: 2022-10-04 05:5Records processed: 182000; time: 2022-10-07 22:1Records processed: 183000; time: 2022-10-11 11:5Records processed: 184000; time: 2022-10-14 23:1Records processed: 185000; time: 2022-10-18 12:5Records processed: 186000; time: 2022-10-22 00:10:00 CEST (1666390200)
Processed 186899 records to rebuild 757 daily summaries in 5017.90 seconds
Rebuild of daily summaries in database 'weewx.sdb' complete

Thank you in advance.

Best regards.

Roberto

[gjr80]

OK, that all looks as expected and importantly no errors. Let's have a look in your database. Can you please start a sqlite3 session with your WeeWX database and execute the following queries:

1. archive rainfall for day of concern:

sqlite> SELECT SUM(rain) FROM archive WHERE dateTime>1601157600 AND dateTime<=1601244000;

2. daily summary rainfall for day of concern:

sqlite> SELECT sum FROM archive_day_rain WHERE dateTime=1601157600;

3. just for kicks let's have a look at the earliest and latest timestamps in the database

sqlite> SELECT MIN(dateTime),datetime(MIN(dateTime), 'unixepoch','localtime'),MAX(dateTime),datetime(MAX(dateTime), 'unixepoch','localtime') FROM archive;

Please post the output of the entire sqlite3 session including the commands issued to start sqlite3 and execute the queries.

Gary

[Roberto Verrelli]

Hi Gary,

thanks in advance for your support,below the command requested:

sqlite> SELECT SUM(rain) FROM archive WHERE dateTime>1601157600 AND dateTime<=1601244000;

1.13385826771654

sqlite> SELECT sum FROM archive_day_rain WHERE dateTime=1601157600;

0.645669291338583

sqlite> SELECT MIN(dateTime),datetime(MIN(dateTime), 'unixepoch','localtime'),MAX(dateTime),datetime(MAX(dateTime), 'unixepoch','localtime') FROM archive;

1600431784|2020-09-18 14:23:04|1666894500|2022-10-27 20:15:00

Thanks

Roberto

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/a203f476-41be-4cd5-8c27-c2b62217fd2cn%40googlegroups.com.

[gjr80]

So the data is still inconsistent. In my last post I asked you to include the exact command you used to start sqlite3; you didn't provide that information - could you please provide that. Also, can you provide a wee_debug report. wee_debug will provide, among other useful to assist in debugging, a sanitised copy of your weewx.conf. Before posting the wee_debug output please check it carefully for sensitive info, wee_debug should obfuscate such information but it is not perfect.

Let's also have a closer look at the actual rain data in the archive for the day concerned. Please start another sqlite3 session and run the following query:

sqlite> SELECT dateTime,datetime(dateTime,'unixepoch','localtime'),rain FROM archive WHERE dateTime>1601157600 AND dateTime<=1601244000 ORDER BY dateTime;

Again please post the command used to invoke sqlite3 as well as the query entered and the result. It is important you post everything asked for.

Gary

[Roberto Verrelli]

Hi Gary,

attached the info requested.

Moreover,to execute the query i perform those steps:

cd /var/lib/weewx

sudo sqlite3 weewx.sdb

Thanks again

Roberto

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/63df44ca-d879-4abf-a49e-8900a6ce3064n%40googlegroups.com.

[gjr80]

Roberto,

You have a lot of odd ball timestamps in your archive but that should not affect things. I'd like to have a closer look at your database and run it through wee_database here where I can monitor what goes on a bit closer. Would you be prepared to email your database to me? You only have a couple of years of data so it should not be too big. My email address is gjroderick at gmail dot com. To be safe just stop WeeWX before copying/attaching the database. There should be no personal/sensitive data in your database. If you have any troubles let me know and we can sort out an alternative.

Can you also confirm that your system timezone is set for Rome (currently GMT+2 but normally (ie non-daylight saving ) GMT+1)?

Gary