Wow! Some serious SQL fu! Very nice.
On Sun, Oct 3, 2021 at 12:20 PM [email protected] <[email protected]> wrote: > Here's an updated query that does better checking and it can delete the > rows or update the temperatures. Test it out on a copy of your database > first. This works for sqlite. Don't know about others. > > Walt > > On Sunday, October 3, 2021 at 6:48:23 AM UTC-5 [email protected] wrote: > >> Exactly. Is there a way to consider only cases such as +0, -10, +0 (i.e. >> spikes) and not those like +0, -6. -8 ? >> Meanwhile I thank you for your precious help. >> Andrea >> >> Il giorno giovedì 30 settembre 2021 alle 23:03:35 UTC+2 [email protected] >> ha scritto: >> >>> Just a reminder, this delete query will delete all situations where the >>> criteria is met. Some of these situations are legit, temps can change >>> rapidly. You want to make sure it's going to delete only the records you >>> want before running it. There are probably some enhancements that can be >>> made to the query to find more obvious spikes. Something like +0, -10, +0 >>> might be a spike, whereas +0, -6. -8 is not, the temp is just falling fast. >>> >>> On Thursday, September 30, 2021 at 1:11:05 PM UTC-5 [email protected] >>> wrote: >>> >>>> Well, make sure you back up your database before you try to delete >>>> anything in case this goes wrong. I would >>>> >>>> 1. Stop weewx >>>> 2. Make a copy of the database >>>> 3. Execute the delete SQL >>>> 4. Run the original query again to see if things are OK >>>> 5. Rebuild weewx dailies >>>> 6. Start weewx >>>> >>>> Hopefully I'm not forgetting anything. >>>> >>>> I'm attaching SQL to delete the spiked records. You will have to >>>> change the database name in the .open statement to the db you want to >>>> change. You may need to run sqlite3 with sudo in order to delete records. >>>> >>>> I thought a better solution would be to update the spiked temp with an >>>> average of the readings surrounding the spike, but I'm having trouble >>>> coming up with the SQL to do that. >>>> >>>> Walt >>>> On Thursday, September 30, 2021 at 9:41:17 AM UTC-5 [email protected] >>>> wrote: >>>> >>>>> Thank you Walt, this script works fine! in fact it highlighted that >>>>> spike in my graph: >>>>> >>>>> DT datetime temp temp_prior >>>>> temp_next variance d_back d_forw >>>>> ------------------- ---------- ---------------- ---------------- >>>>> ---------------- ---------- ------------------ ---------------- >>>>> 2021-09-30 04:15:00 1632968100 19.2970370370371 19.3363567649282 >>>>> 14.5003779289494 2.38 0.0393197278911508 4.79665910808766 >>>>> >>>>> (I added two columns d_back = |t1-t2| and t_forw = |t1-t3|) >>>>> Now, I need to delete the wrong record: in this case, it is t3 (d_forw >>>>> = 4.8). How can I automatically find it and then remove? Unfortunately I >>>>> don't know the sql language and I can't complete the script myself. >>>>> >>>>> Thank you very much, >>>>> Andrea >>>>> Il giorno giovedì 30 settembre 2021 alle 15:27:37 UTC+2 >>>>> [email protected] ha scritto: >>>>> >>>>>> Here's a query using the "join a table to itself" method if you don't >>>>>> have the LAG function. It prints observations that are outside the >>>>>> average >>>>>> of the surrounding observations by 2+ degrees. I get about 30 >>>>>> observations >>>>>> this year and I think it's legit. Although I don't get spikes in my >>>>>> data, >>>>>> I do use a 15 minute interval and my station is closer to the ground than >>>>>> it should be. A lot can happen in 15 minutes and my temp graph is always >>>>>> kinda wiggly. >>>>>> >>>>>> Caveat Emptor on the attached query. You could modify it to delete >>>>>> or update the offending rows instead of printing them out. >>>>>> >>>>>> Walt >>>>>> >>>>>> >>>>>> On Thursday, September 30, 2021 at 7:52:41 AM UTC-5 [email protected] >>>>>> wrote: >>>>>> >>>>>>> Yes, it's possible, but it's not a simple SELECT statement. For >>>>>>> example, this query will return all rows where the temperature >>>>>>> difference >>>>>>> between adjacent rows is greater than 1 degree: >>>>>>> >>>>>>> >>>>>>> *SELECT g.* FROM (SELECT dateTime, >>>>>>> datetime(dateTime,'unixepoch','localtime'), LAG(outTemp) OVER (ORDER BY >>>>>>> dateTime) AS prev_outTemp, outTemp FROM archive) AS g WHERE >>>>>>> ABS(g.outTemp-g.prev_outTemp) > 1;* >>>>>>> >>>>>>> For my own database, this returns >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> *1192323300|2007-10-13 17:55:00|64.099998|63.01192382100|2007-10-14 >>>>>>> 10:15:00|51.900002|53.01192756800|2007-10-18 >>>>>>> 18:20:00|44.700001|46.7000011192757100|2007-10-18 >>>>>>> 18:25:00|46.700001|49.51192757400|2007-10-18 18:30:00|49.5|47.400002* >>>>>>> ... >>>>>>> >>>>>>> You would then need to arrange to delete those specific timestamps. >>>>>>> >>>>>>> The select statement will only work with modern versions of sqlite, >>>>>>> which have the LAG() function. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Thu, Sep 30, 2021 at 3:35 AM [email protected] <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>>> Hi there, >>>>>>>> I need to remove some temperature spikes from my weewx.sdb >>>>>>>> database. I know this has been already asked many times, but my case is >>>>>>>> different. I need to delete spikes in which the values are climatically >>>>>>>> acceptable: so, it is not sufficient to give (temperature is intended >>>>>>>> in °C) >>>>>>>> >>>>>>>> echo "delete from archive where outTemp < -6;" | sudo sqlite3 >>>>>>>> /home/weewx/archive/weewx.sdb >>>>>>>> >>>>>>>> for solving the problem. In my case, the spike was about 16°C when >>>>>>>> temperature was about 20°C (see the graph below) >>>>>>>> [image: daytempdew.png] >>>>>>>> What I need to do is something like: DELETE if Δt = 300 AND |ΔT| > >>>>>>>> 5, where >>>>>>>> Δt is time difference (in seconds, I chose 300 because my archive >>>>>>>> interval is 5 minutes) and ΔT is the temperature difference in that >>>>>>>> time >>>>>>>> bin. >>>>>>>> In other words I am thinking about using a differential approach. >>>>>>>> It seems to me that this can be the only solution, is this possible >>>>>>>> with >>>>>>>> sqlite3? >>>>>>>> Thank you, >>>>>>>> Andrea >>>>>>>> >>>>>>>> -- >>>>>>>> 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/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%40googlegroups.com >>>>>>>> <https://groups.google.com/d/msgid/weewx-user/32298c8c-6d93-4a80-8b9e-29fdcc181c8an%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/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/a3b5661f-58d6-4c17-8ce2-4c55ca620eb1n%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/CAPq0zEAVyJgU_AhDj5xt74_xrJidRm%3DvUQQSnNKnv5z3VQW%2B8Q%40mail.gmail.com.
