You can do this with the sqlite3 utility program. I believe the sql update statement you want is:
update archive set signal4 = case when outtemp < 10.0 then 1.0 else 0.0 end; I tried it on a copy of my database, see attached. Walt On Tuesday, July 30, 2024 at 7:03:20 PM UTC-5 Craig Young wrote: > Good advice Peter .. I will give this a try. > > Craig > > On Wednesday, July 31, 2024 at 11:18:26 AM UTC+12 p q wrote: > >> if I were doing it, I would use SQLite to execute the queries and then I >> would check my work with DB Browser. >> >> I think you may have misunderstood my backup comment. I would practice on >> a copy of my database. Once I had the queries correct, then I'd stop Weewx, >> make another backup of the database to be safe, perform the operation, and >> then restart Weewx. If you're sure you won't mess up, you can skip practice >> and the extra backup. >> >> On Tue, Jul 30, 2024 at 4:09 PM Craig Young <craig.y...@gmail.com> wrote: >> >>> Absolutely, I would backup the database first .. your idea of using a >>> two pass method would work .. would I do that with the weewx database >>> utility or some linux based sql app like DB Browser for SQLite? >>> >>> On Wednesday, July 31, 2024 at 10:45:47 AM UTC+12 p q wrote: >>> >>>> Do yourself a favor and make a backup copy. Test out whatever method >>>> you chose on the copy. >>>> >>>> As to the query, I think you could fill the Signal field with 0.0 and >>>> then do an UPDATE SQL query where outTemp > 10.0. You can find the SQL >>>> syntax online. >>>> >>>> On Tue, Jul 30, 2024 at 3:30 PM Craig Young <craig.y...@gmail.com> >>>> wrote: >>>> >>>>> WeeWx version: 4.10.2 >>>>> DB: SQLite3 >>>>> >>>>> What is the best method for recalculating an item in the entire >>>>> database? Specifically, I want to do this: >>>>> >>>>> signal4 = 1.0 if outTemp < 10.0 else 0.0 >>>>> >>>>> So I want the DB update method to look at each record and if the >>>>> temperature (in that record) is less than 10.0 C then set the signal4 >>>>> value >>>>> in that record to 1.0, otherwise, set the signal4 value to 0.0. >>>>> >>>>> Do I use wee_database --calc missing? >>>>> or some other method? >>>>> >>>>> There are currently 575,000 records in the database so I really don't >>>>> want to mess this up. I assume I need to stop WeeWx while doing this? >>>>> >>>>> Craig >>>>> >>>>> -- >>>>> 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/df5146de-9a51-4c05-888a-17a663d774f4n%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/weewx-user/df5146de-9a51-4c05-888a-17a663d774f4n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> >>>> >>>> -- >>>> Peter Quinn >>>> (415)794-2264 <(415)%20794-2264> >>>> >>> -- >>> 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/119eea81-a564-4b8c-b84f-3b9bb857e6d6n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/weewx-user/119eea81-a564-4b8c-b84f-3b9bb857e6d6n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> >> >> -- >> Peter Quinn >> (415)794-2264 <(415)%20794-2264> >> > -- 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/a395b8bf-5ad0-4857-9e3f-77b3dcb45739n%40googlegroups.com.
Stop weewx before messing with the database. You can update the database with the sqlite3 utility program. Yes definitely do this on a backup first to make sure you get the result you want. Then I'd copy the live database again, apply the change to the copy, verify it is correct and then replace the existing database with the updated copy i believe the SQL update statement you want is: update archive set signal4 = case when outtemp < 10.0 then 1.0 else 0.0 end; Proof using a copy of my database; i used heatingvoltage because i don't have signal4 and heatingvoltage isn't used in my setup. SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open weewx.sdb sqlite> .mode columns sqlite> .headers on sqlite> select signal4 from archive; Error: no such column: signal4 sqlite> select heatingvoltage from archive limit 1; heatingVoltage -------------- sqlite> select case when outtemp < 10.0 then '<10.0' else '>=10.0' end as temp, heatingvoltage, count(*) ...> from archive ...> group by temp, heatingvoltage; temp heatingVoltage count(*) ------ -------------- -------- <10.0 1985 >=10.0 184785 sqlite> update archive set heatingvoltage = case when outtemp < 10.0 then 1.0 else 0.0 end; sqlite> select case when outtemp < 10.0 then '<10.0' else '>=10.0' end as temp, heatingvoltage, count(*) ...> from archive ...> group by temp, heatingvoltage; temp heatingVoltage count(*) ------ -------------- -------- <10.0 1.0 1985 >=10.0 0.0 184785 sqlite> .exit
