SQLite3 calculate an item

[Craig Young]

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

[p q]

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.

--
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.

--

Peter Quinn
(415)794-2264

[Craig Young]

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?

[p q]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/119eea81-a564-4b8c-b84f-3b9bb857e6d6n%40googlegroups.com.

--

Peter Quinn
(415)794-2264

[Craig Young]

Good advice Peter .. I will give this a try.

Craig

[wfs...@gmail.com]

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

[Craig Young]

Thank you heaps Walt .. exactly what I was looking for.

Craig