Sorry for posting here. I had the null values before that you helped me with, so I wasn't sure if this was another issue. Thanks for checking them.
On Saturday, January 9, 2021 at 1:02:13 PM UTC-5 [email protected] wrote: > Oh, sorry! I had just assumed they were from you. > > Michael: those results look fine. Why? Is there a concern? > > -t > > On Sat, Jan 9, 2021 at 9:54 AM Arend <[email protected]> wrote: > >> Hello Tom, >> >> That last query is not mine (please check the names/e-mail adresses), I >> am not sure why he is posting his query here but the output seems to be ok. >> :-) >> >> Arend >> >> Op zaterdag 9 januari 2021 om 17:44:29 UTC+1 schreef [email protected]: >> >>> Those are very different results from the ones you showed before. You'll >>> have to be the judge of whether minimums were around 2.4, or around 26.24. >>> That's a big difference, so it should be fairly obvious. >>> >>> -tk >>> >>> On Sat, Jan 9, 2021 at 7:03 AM Michael Sanphillipo <[email protected]> >>> wrote: >>> >>>> Tom, here are my results. Do these look okay? >>>> >>>> 1548937162|2019-01-31 00:00:00|2.4 >>>> 1548910187|2019-01-30 00:00:00|6.4 >>>> 1549090774|2019-02-02 00:00:00|6.6 >>>> 1548998381|2019-02-01 00:00:00|7.5 >>>> 1548071996|2019-01-21 00:00:00|10.0 >>>> 1548160788|2019-01-22 00:00:00|10.8 >>>> 1517661600|2018-02-03 00:00:00|11.2 >>>> 1516278240|2018-01-18 00:00:00|11.6 >>>> 1516013940|2018-01-15 00:00:00|13.1 >>>> 1581768000|2020-02-15 00:00:00|13.3 >>>> 1515933960|2018-01-14 00:00:00|13.5 >>>> 1517401200|2018-01-31 00:00:00|13.6 >>>> 1547554197|2019-01-15 00:00:00|13.6 >>>> 1579610280|2020-01-21 00:00:00|13.8 >>>> 1547528389|2019-01-14 00:00:00|15.3 >>>> 1548046482|2019-01-20 00:00:00|15.3 >>>> 1551946191|2019-03-07 00:00:00|15.3 >>>> 1549800883|2019-02-10 00:00:00|15.9 >>>> 1576836720|2019-12-20 00:00:00|16.0 >>>> 1579582320|2020-01-20 00:00:00|16.0 >>>> >>>> >>>> On Friday, January 8, 2021 at 5:03:00 PM UTC-5 Arend wrote: >>>> >>>>> Adding that extra testing for NOT NULL did the trick. All dates (as >>>>> far as I can tell) are now correctly retreived. >>>>> >>>>> Op vrijdag 8 januari 2021 om 22:33:50 UTC+1 schreef [email protected]: >>>>> >>>>>> It appears that you have a null value for mintime in your database. >>>>>> Most likely the problem is that you have no data for 2019-12-15 and >>>>>> 2019-12-17. >>>>>> >>>>>> I suspect the older query worked because MIN(value) ignores values, >>>>>> while ordering by value does not. >>>>>> >>>>>> This is a bug: the query should check for non null values. I've >>>>>> created issue 635 <https://github.com/weewx/weewx/issues/635> to >>>>>> track, and (hopefully) fixed in commit 5fbe0d5 >>>>>> <https://github.com/weewx/weewx/commit/5fbe0d51e88cfb126543e5309a970be220e7dcc0> >>>>>> . >>>>>> >>>>>> Could you please try this version of xtypes.py >>>>>> <https://raw.githubusercontent.com/weewx/weewx/master/bin/weewx/xtypes.py> >>>>>> ? >>>>>> >>>>>> Thanks for your help. >>>>>> >>>>>> On Fri, Jan 8, 2021 at 12:49 PM Arend <[email protected]> wrote: >>>>>> >>>>>>> SELECT mintime, from_unixtime(dateTime), min from >>>>>>> archive_day_outTemp order by min asc, mintime asc limit 20; >>>>>>> >>>>>>> Database weewx >>>>>>> >>>>>>> mintime from_unixtime(dateTime) min >>>>>>> >>>>>>> NULL 2019-12-15 00:00:00 NULL >>>>>>> >>>>>>> NULL 2019-12-17 00:00:00 NULL >>>>>>> >>>>>>> 1606706235 2020-11-30 00:00:00 26.24 >>>>>>> >>>>>>> 1585551900 2020-03-30 00:00:00 27.32 >>>>>>> >>>>>>> 1606689987 2020-11-29 00:00:00 27.5 >>>>>>> >>>>>>> 1575188400 2019-12-01 00:00:00 27.86 >>>>>>> >>>>>>> 1585724700 2020-04-01 00:00:00 28.94 >>>>>>> >>>>>>> 1575533700 2019-12-05 00:00:00 29.66 >>>>>>> >>>>>>> 1585639200 2020-03-31 00:00:00 30.02 >>>>>>> >>>>>>> 1574240700 2019-11-20 00:00:00 30.56 >>>>>>> >>>>>>> 1580890500 2020-02-05 00:00:00 30.92 >>>>>>> >>>>>>> 1607632800 2020-12-10 00:00:00 31.27999999999998 >>>>>>> >>>>>>> 1575153900 2019-11-30 00:00:00 31.28 >>>>>>> >>>>>>> 1579598100 2020-01-21 00:00:00 31.28 >>>>>>> >>>>>>> 1585029900 2020-03-24 00:00:00 31.46 >>>>>>> >>>>>>> 1609479829 2021-01-01 00:00:00 31.46 >>>>>>> >>>>>>> 1575255300 2019-12-02 00:00:00 31.82 >>>>>>> >>>>>>> 1584945300 2020-03-23 00:00:00 31.82 >>>>>>> >>>>>>> 1585116900 2020-03-25 00:00:00 31.82 >>>>>>> >>>>>>> 1607641210 2020-12-11 00:00:00 31.82 >>>>>>> Op vrijdag 8 januari 2021 om 21:18:54 UTC+1 schreef [email protected] >>>>>>> : >>>>>>> >>>>>>>> Then adapt as necessary. Use from_unixtime() instead of datetime() >>>>>>>> in the select statement. >>>>>>>> >>>>>>>> On Fri, Jan 8, 2021 at 11:49 AM Arend <[email protected]> wrote: >>>>>>>> >>>>>>>>> Hello Tom, >>>>>>>>> >>>>>>>>> I am using MySQL database. >>>>>>>>> >>>>>>>>> Arend >>>>>>>>> >>>>>>>>> Op vrijdag 8 januari 2021 om 20:18:20 UTC+1 schreef >>>>>>>>> [email protected]: >>>>>>>>> >>>>>>>>>> Thanks. Not sure what is happening, but let's take a look at your >>>>>>>>>> database. I'm assuming you are using SQLite, and that you used a >>>>>>>>>> package >>>>>>>>>> installer. You may have to install the tool sqlite3 first >>>>>>>>>> >>>>>>>>>> sudo apt-get install sqlite3 >>>>>>>>>> >>>>>>>>>> Then >>>>>>>>>> >>>>>>>>>> *sqlite3 /var/lib/weewx/weewx.sdb* >>>>>>>>>> sqlite> *select mintime, >>>>>>>>>> datetime(dateTime,'unixepoch','localtime'), min from >>>>>>>>>> archive_day_outTemp >>>>>>>>>> order by min asc, mintime asc limit 20;* >>>>>>>>>> sqlite> *.quit* >>>>>>>>>> >>>>>>>>>> Let me know what you get. >>>>>>>>>> >>>>>>>>>> -tk >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Fri, Jan 8, 2021 at 10:01 AM Arend <[email protected]> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> After upgrading from WeeWX 4.1.1 to 4.3.0 Invalid dates started >>>>>>>>>>> to show up for mintime. >>>>>>>>>>> >>>>>>>>>>> In this Belchertown thread the issue is reported by multiple >>>>>>>>>>> users. >>>>>>>>>>> >>>>>>>>>>> https://github.com/poblabs/weewx-belchertown/issues/517 >>>>>>>>>>> >>>>>>>>>>> After replacing the SQL optimizations with the previous >>>>>>>>>>> "mintime" SQL strings from WeeWX 4.1.1 the problem was solved. >>>>>>>>>>> >>>>>>>>>>> Screenshot using optimized (4.3.0) SQL showing invalid dates >>>>>>>>>>> using "mintime": >>>>>>>>>>> >>>>>>>>>>> [image: weewx_belchertown_records_invalid_dates.png] >>>>>>>>>>> >>>>>>>>>>> Replacing the relevant "mintime" code in xtypes.py with SQL >>>>>>>>>>> strings from 4.1.1: >>>>>>>>>>> >>>>>>>>>>> [image: agg_sql_dict.png] >>>>>>>>>>> [image: daily_sql_dict.png] >>>>>>>>>>> >>>>>>>>>>> Now the previous invalid dates are displayed correctly: >>>>>>>>>>> >>>>>>>>>>> [image: Records Belchertown na herstel SQL strings.png] >>>>>>>>>>> >>>>>>>>>>> Haven't tested other SQL optimizations. >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> 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/3c48aa67-0850-4f19-af9e-a08527ef8b1cn%40googlegroups.com >>>>>>>>>>> >>>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/3c48aa67-0850-4f19-af9e-a08527ef8b1cn%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/d4d920a7-1bd9-4fa7-91d5-d187a6cda3cfn%40googlegroups.com >>>>>>>>> >>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/d4d920a7-1bd9-4fa7-91d5-d187a6cda3cfn%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/80807b3d-0797-4bc8-b582-013a4e40eacdn%40googlegroups.com >>>>>>> >>>>>>> <https://groups.google.com/d/msgid/weewx-user/80807b3d-0797-4bc8-b582-013a4e40eacdn%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/c209d7d1-be4b-48c9-acdd-00562d9190abn%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/weewx-user/c209d7d1-be4b-48c9-acdd-00562d9190abn%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/c840d71e-db1e-4b65-a244-bbc1cdc8f330n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/weewx-user/c840d71e-db1e-4b65-a244-bbc1cdc8f330n%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/e85b0bb6-a4aa-4a08-b6c8-011688f86b36n%40googlegroups.com.
