I save my database hourly, keep these snapshots for a day, keep daily snapshots for a month... and so on.
>From a database snapshot before I upgraded, archive_day__metadata says: "Version 2.0" It didn't change during the update. Here is an entry of "archive_day_outTemp": (also from a snapshot before the update) 1604876400 -0.100000000000001 1604903428 9.3 1604925619 1065.95282828283 288 319785.848484848 86400 After the update weewx was producing these lines (first and second), the third line is the day with the update, fourth and fifth is before the update. (from a snapshot after the update, but before my sql-patch) 1605481200 1.4 1605493171 8.9 1605535929 1811.52088383838 288 1811.52088383838 288 1605394800 2.1 1605473931 16.1 1605445486 2346.52735930736 288 2346.52735930736 288 1605308400 5.5 1605328978 17.3 1605360608 2590.47363636364 288 650228.309292929 69058 1605222000 4.0 1605238302 14.9 1605272440 2255.38090909091 288 676614.272727273 86400 1605135600 2.7 1605160230 12.0 1605186834 1942.27053030303 288 582681.159090909 86400 So the behavior changed after the update. If I now alter the database with your suggested queries, I'm afraid ending up having the unweigthed entries written, like weewx now writes them and then having the problem all over again. Here are some lines of a friend's database. He didn't observe any problems with his average values: 1604098800 5.1 1604184954 15.8 1604153156 3087.89794733045 288 3087.89794733045 288 1604012400 6.9 1604022829 12.6 1604059984 2970.48686868687 288 2970.48686868687 288 1603926000 5.6 1603926667 9.7 1603970504 2352.8623989899 288 235090.302070707 30188 1603839600 -1.2 1603859547 11.9 1603896779 1507.83905483406 288 452351.716450217 86400 1603753200 0.29997 1603839591 9.9 1603808400 1324.44158008658 288 397332.474025974 86400 I let you guess, which day he updated to 4.2.0 :) But his average calculation still works. He also has "Version 2.0", but he doesn't have a db snapshot from a day before the update. tke...@gmail.com schrieb am Montag, 30. November 2020 um 15:20:21 UTC+1: > Michael, I have no idea what happened, but if you want to modify your > daily summaries, it should be done right. If I understand you correctly, > you patched them with SQL commands like > > update archive_day_ET set wsum=sum, sumtime=count; > update archive_day_UV set wsum=sum, sumtime=count; > update archive_day_altimeter set wsum=sum, sumtime=count; > etc. > > This will leave your summaries in an inconsistent state: the numbers are > what would be expected for a Version 1 summary, yet your metadata will say > Version 2. Furthermore, as new days are added, Version 2 numbers will be > used, and you'll be back where you started: with half Version 1 and half > Version 2. > > I would suggest making it totally Version 2. Do an update, except weight > them with the archive interval. This will work if your archive interval is > a constant 5 minutes (300 seconds): > > 1. Stop weewx. > 2. Make a backup of weewx.sdb > 3. Use updates that look like: > > update archive_day_ET set wsum=sum*300, sumtime=count*300; > update archive_day_UV set wsum=sum*300, sumtime=count*300; > update archive_day_altimeter set wsum=sum*300, sumtime=count*300; > etc. > > 4. Restart weewx > > > > On Sun, Nov 29, 2020 at 11:36 AM michael.k...@gmx.at <michael.k...@gmx.at> > wrote: > >> >> https://imgur.com/a/yZUPoTq >> > michael.k...@gmx.at schrieb am Samstag, 28. November 2020 um 09:27:44 >> UTC+1: >> > >>> I am observing the same situation, as well as other WeeWX users near me. >>> The average is clearly off since the 4.2.0 update. It also affects yearly >>> average since then. So I guess this is something that happened with the >>> 4.2.0 Version. I don't want to rebuild my daily values, how to find the bad >>> values and correct them? Probably in the archive_daily table of the day I >>> made the update? >>> >>> I found something: It's a change with "sum": >>> 1-13 has "new" sumtime, 14 a mix (the day I updated) and old sumtime are >>> 15 and and older: >>> >>> Isn't there a config that sets how this is calculated? >>> >>> >>> b.cl...@gmail.com schrieb am Donnerstag, 19. November 2020 um 19:43:57 >>> UTC+1: >>> >>>> Yeah, everything looks great again. >>>> Thank you Tom for that excellent support. >>>> Greetings from Suedlohn (Germany). >>>> Berny >>>> >>>> tke...@gmail.com schrieb am Donnerstag, 19. November 2020 um 17:56:56 >>>> UTC+1: >>>> >>>>> So, for some reason, the weighted sum (field 'wsum') has too high a >>>>> value, or the sum of observation time (field 'sumtime') has too low a >>>>> value. >>>>> >>>>> The easiest fix is to just rebuild the daily summaries using the >>>>> wee_database >>>>> utility <http://www.weewx.com/docs/utilities.htm#wee_database_utility> >>>>> . >>>>> >>>>> Stop weewxd. then, >>>>> >>>>> *wee_database --drop-daily* >>>>> *wee_database --rebuild-daily* >>>>> >>>>> Restart weewxd >>>>> >>>>> For a database of your size, it shouldn't take more than a minute or >>>>> two. >>>>> >>>>> It could take some time for the NOAA and html files to get corrected. >>>>> You can speed things up by deleting them and allowing weewx to regenerate >>>>> them. >>>>> >>>>> -tk >>>>> >>>>> >>>>> >>>>> On Thu, Nov 19, 2020 at 8:48 AM Berny Cl <b.cl...@gmail.com> wrote: >>>>> >>>>>> sqlite> select sum(wsum)/sum(sumtime) from archive_day_outTemp where >>>>>> strftime("%Y-%m",dateTime,'unixepoch','localtime')=='2020-11'; >>>>>> 60.1308595259353 >>>>>> >>>>>> Ok, that looks the same like the value in my history table. >>>>>> >>>>>> tke...@gmail.com schrieb am Donnerstag, 19. November 2020 um >>>>>> 17:13:38 UTC+1: >>>>>> >>>>>>> 1. That looks reasonable. One other query to try: >>>>>>> >>>>>>> sqlite> *select sum(wsum)/sum(sumtime) from archive_day_outTemp >>>>>>> where strftime("%Y-%m",dateTime,'unixepoch','localtime')=='2020-11';* >>>>>>> >>>>>>> 2. If that doesn't reveal anything, I will send you an instrumented >>>>>>> version of xtypes.py that will log the calculation. >>>>>>> >>>>>>> >>>>>>> On Thu, Nov 19, 2020 at 6:44 AM Berny Cl <b.cl...@gmail.com> wrote: >>>>>>> >>>>>>>> sqlite> select avg(outTemp) from archive where strftime("%Y-%m", >>>>>>>> dateTime,'unixepoch','localtime')=='2020-11'; >>>>>>>> 51.117818676717 <(781)%20867-6717> >>>>>>>> sqlite> select sum(sum)/sum(count) from archive_day_outTemp where >>>>>>>> strftime("%Y-%m",dateTime,'unixepoch','localtime')=='2020-11'; >>>>>>>> 51.114923603352 >>>>>>>> >>>>>>>> Thank you! >>>>>>>> OK, I did that. The two numbers are very close. I think they are >>>>>>>> correct (in Fahrenheit) but in my history table the temperature ist >>>>>>>> too >>>>>>>> high (in degree Celsius). >>>>>>>> >>>>>>>> tke...@gmail.com schrieb am Donnerstag, 19. November 2020 um >>>>>>>> 15:02:55 UTC+1: >>>>>>>> >>>>>>>>> Most likely it's some bad data. Let's check the database directly. >>>>>>>>> >>>>>>>>> First find the database. If you did a package install, it's most >>>>>>>>> likely at /var/lib/weewx/weewx.sdb. If you did a setup.py install, >>>>>>>>> it's at >>>>>>>>> /home/weewx/archive/weewx.sdb. Let's assume the former. >>>>>>>>> >>>>>>>>> Then, run two queries: >>>>>>>>> >>>>>>>>> *sqlite /var/lib/weewx/weewx.sdb* >>>>>>>>> sqlite> *select avg(outTemp) from archive where strftime("%Y-%m", >>>>>>>>> dateTime,'unixepoch','localtime')=='2020-11';* >>>>>>>>> sqlite> *select sum(sum)/sum(count) from archive_day_outTemp >>>>>>>>> where strftime("%Y-%m",dateTime,'unixepoch','localtime')=='2020-11';* >>>>>>>>> >>>>>>>>> The first method calculates the average temperature for Nov 2020 >>>>>>>>> by using the main archive table. The second by using the daily >>>>>>>>> summaries. >>>>>>>>> The two numbers should be very close. See what you get and we'll take >>>>>>>>> it from there. >>>>>>>>> >>>>>>>>> -tk >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, Nov 19, 2020 at 4:43 AM Berny Cl <b.cl...@gmail.com> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Hi everybody, >>>>>>>>>> since the last update to version 4.20, i have noticed an >>>>>>>>>> incorrect value for the monthly average temperature at the history >>>>>>>>>> table >>>>>>>>>> and also in the monthly NOAA table. >>>>>>>>>> I use the niculskin and my station is a FineOffset (WS 1080). >>>>>>>>>> >>>>>>>>>> See at: http://haus-volmering.de/history.html >>>>>>>>>> (Durchschnittstemperatur = Average Temperature for Nov is >>>>>>>>>> obviously incorrect) >>>>>>>>>> >>>>>>>>>> How can I fix that? >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> 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/277ec811-21b4-41e9-8fc1-cda38d87014dn%40googlegroups.com >>>>>>>>>> >>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/277ec811-21b4-41e9-8fc1-cda38d87014dn%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 weewx-user+...@googlegroups.com. >>>>>>>> >>>>>>> To view this discussion on the web visit >>>>>>>> https://groups.google.com/d/msgid/weewx-user/87243ab4-47a4-4c85-b004-530f84e77673n%40googlegroups.com >>>>>>>> >>>>>>>> <https://groups.google.com/d/msgid/weewx-user/87243ab4-47a4-4c85-b004-530f84e77673n%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 weewx-user+...@googlegroups.com. >>>>>> >>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/weewx-user/f5a4768c-27f7-49c6-807e-496296d0c380n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/weewx-user/f5a4768c-27f7-49c6-807e-496296d0c380n%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 weewx-user+...@googlegroups.com. >> > To view this discussion on the web visit >> https://groups.google.com/d/msgid/weewx-user/c720db5a-0470-43e8-9044-f73db6ccd307n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/weewx-user/c720db5a-0470-43e8-9044-f73db6ccd307n%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 weewx-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/4258df07-5b9e-4787-a5c4-6436b1c8ab43n%40googlegroups.com.
