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 On Wednesday, 26 October 2022 at 03:06:34 UTC+10 [email protected] wrote: > 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 > > Il giorno domenica 23 ottobre 2022 alle 23:00:03 UTC+2 gjr80 ha scritto: > >> 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 >> On Monday, 24 October 2022 at 00:35:02 UTC+10 [email protected] wrote: >> >>> 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 >>> >>> Il giorno domenica 23 ottobre 2022 alle 13:44:45 UTC+2 gjr80 ha scritto: >>> >>>> 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 >>>> >>>> On Sunday, 23 October 2022 at 19:27:59 UTC+10 Roberto Verrelli wrote: >>>> >>>>> Hi gary, >>>>> As i reported,I already did every step you suggested,data on >>>>> archive_daily_rain is still wrong.... >>>>> >>>>> Il dom 23 ott 2022, 10:42 gjr80 <[email protected]> ha scritto: >>>>> >>>>>> 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 >>>>>> <http://weewx.com/docs/utilities.htm#wee_database_utility> 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 >>>>>> On Sunday, 23 October 2022 at 17:09:27 UTC+10 [email protected] >>>>>> wrote: >>>>>> >>>>>>> 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 >>>>>>> >>>>>> -- >>>>>> 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 >>>>>> [email protected]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/weewx-user/fab0df47-320b-478c-bed9-717ae22d2f68n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/weewx-user/fab0df47-320b-478c-bed9-717ae22d2f68n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/a203f476-41be-4cd5-8c27-c2b62217fd2cn%40googlegroups.com.
