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 anc...@gmail.com <ance...@gmail.com> 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 weewx-user+unsubscr...@googlegroups.com. > 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 weewx-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEDk%3D7m33mg8_GgGyb6WROwhvDSdxPjEs4g1itjVKCWTKg%40mail.gmail.com.
