Nicely done. Thanks, Cameron! On Mon, Oct 28, 2019 at 8:17 AM Cameron D <[email protected]> wrote:
> Wiki has been written up. *Do not use the code I posted earlier in this > thread *- there are mistakes in it. > > It took a bit longer than expected, as the code needed significant > reworking for various reasons.. > I (foolishly) decided to test out the code with my database, that has > about 3 years of samples at 1 minute interval - about 1.5 million rows - > and to make sure it works on mysql. > > As a point of reference I later wrote an awk script to consolidate the > wind gust data on a csv file (without the extraneous data fields) and it > processed the entire 1.5 million records in 1.5 seconds. > On the other hand, using my original script, I had to kill the MariaDB > server process after an hour or so. Mysql server on windows and sqlite > gave similar times - once I cut the database down to times I could measure > to completion. > It turns out trying to calculate the rounded date/time in the join on > windgust speed turned the process from linear to N-squared. Adding an extra > precalculated time column rounded to the 5 minute value returned it to > sensible times. > > I am at a loss to understand Pat's problem with floor(). It certainly > worked for me with the sqlite browser. But it is redundant in any case, I > will just mention that I have noticed the sqlite browser sometimes > reporting 0 rows modified when I expected changes, and everything still > seemed to work as expected. > > > On Sunday, 20 October 2019 05:50:28 UTC+10, Pat wrote: >> >> Latest queries worked well for me but I had to remove the FLOOR() >> statement. Even after loading the math extension pack for SQLite Browser. >> It didn't give me an error it just said "modified 0 rows". Removing it made >> it modify the rows it needed to. >> >> -- > You received this message because you are subscribed to the Google Groups > "weewx-development" 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-development/8c46083f-e681-4160-aaf8-414c72947f00%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-development/8c46083f-e681-4160-aaf8-414c72947f00%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "weewx-development" 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-development/CAPq0zEDmQ4_Yoy4P0ZW%3D%2Bmsca1nTx4e74fjjLVJf0N4yQtRzGQ%40mail.gmail.com.
