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 Il gio 27 ott 2022, 11:16 gjr80 <[email protected]> ha scritto: > 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 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/a203f476-41be-4cd5-8c27-c2b62217fd2cn%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/a203f476-41be-4cd5-8c27-c2b62217fd2cn%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/CAGvLVeiQN3SVJwx%3DF%3DBju6MKW5ftn1tYRQe89SkOsPzgZUsRXA%40mail.gmail.com.
