Latest WeeWX 4.3.0 SQL optimizations may return invalid results

[Arend]

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":

Replacing the relevant "mintime" code in xtypes.py with SQL strings from 4.1.1:

Now the previous invalid dates are displayed correctly:

Haven't tested other SQL optimizations.

[Tom Keffer]

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

--
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/3c48aa67-0850-4f19-af9e-a08527ef8b1cn%40googlegroups.com.

[Arend]

Hello Tom,

I am using MySQL database.

Arend

Op vrijdag 8 januari 2021 om 20:18:20 UTC+1 schreef tke...@gmail.com:

[Tom Keffer]

Then adapt as necessary. Use from_unixtime() instead of datetime() in the select statement.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/d4d920a7-1bd9-4fa7-91d5-d187a6cda3cfn%40googlegroups.com.

[Arend]

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 tke...@gmail.com:

[Tom Keffer]

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 to track, and (hopefully) fixed in commit 5fbe0d5.

Could you please try this version of xtypes.py?

Thanks for your help.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/80807b3d-0797-4bc8-b582-013a4e40eacdn%40googlegroups.com.

[Arend]

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 tke...@gmail.com:

[Michael Sanphillipo]

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

[Tom Keffer]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/c209d7d1-be4b-48c9-acdd-00562d9190abn%40googlegroups.com.

[Arend]

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 tke...@gmail.com:

[Tom Keffer]

Oh, sorry! I had just assumed they were from you. 

Michael: those results look fine. Why? Is there a concern?

-t

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/c840d71e-db1e-4b65-a244-bbc1cdc8f330n%40googlegroups.com.

[Michael Sanphillipo]

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.