Via a dump of every column in the table that exists, whether I'm using it or not, the csv content could be:
"dateTime","usUnits","interval","altimeter","appTemp","appTemp1","barometer","batteryStatus1","batteryStatus2","batteryStatus3","batteryStatus4","batteryStatus5","batteryStatus6","batteryStatus7","batteryStatus8","cloudbase","co","co2","consBatteryVoltage","dewpoint","dewpoint1","ET","extraHumid1","extraHumid2","extraHumid3","extraHumid4","extraHumid5","extraHumid6","extraHumid7","extraHumid8","extraTemp1","extraTemp2","extraTemp3","extraTemp4","extraTemp5","extraTemp6","extraTemp7","extraTemp8","forecast","hail","hailBatteryStatus","hailRate","heatindex","heatindex1","heatingTemp","heatingVoltage","humidex","humidex1","inDewpoint","inHumidity","inTemp","inTempBatteryStatus","leafTemp1","leafTemp2","leafWet1","leafWet2","lightning_distance","lightning_disturber_count","lightning_energy","lightning_noise_count","lightning_strike_count","luminosity","maxSolarRad","nh3","no2","noise","o3","outHumidity","outTemp","outTempBatteryStatus","pb","pm10_0","pm1_0","pm2_5","pressure","radiation","rain","rainBatteryStatus","rainRate","referenceVoltage","rxCheckPercent","signal1","signal2","signal3","signal4","signal5","signal6","signal7","signal8","snow","snowBatteryStatus","snowDepth","snowMoisture","snowRate","so2","soilMoist1","soilMoist2","soilMoist3","soilMoist4","soilTemp1","soilTemp2","soilTemp3","soilTemp4","supplyVoltage","txBatteryStatus","UV","uvBatteryStatus","windBatteryStatus","windchill","windDir","windGust","windGustDir","windrun","windSpeed","pm4_0","pm4_0out","pm2_5out","pm1_0out","pm10_0out","co2out" 1741612440,1,1,,,,,1.0,,,1.0,,,,,,,,,,,,58.0,,,58.0,,,,,75.02,,,77.36,,,,,,,,,,,,,,,,,,,,,,,,,,,,,238.51020260006104,,,,,,,,,,,,, 0.00999881889763705 ,,0.0199976377952741,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, where the larger font size one is the rain amount that I'm inserting. Then one observation line for each record period that I need to change (that way, no change is required to the column name line for periods that have a different mix of observations with data)? Only 8 observations got recorded in that period I have above (I have more than 8 XXXbatteryStatus observations alone), but at this point I'm just seeking to add the rain observation. On Fri, Mar 14, 2025 at 9:41 PM gjr80 <[email protected]> wrote: > See comments below. > > Gary > > On Saturday, 15 March 2025 at 03:47:33 UTC+10 Andrew McGinnis wrote: > > If you want to use weectl import to import data into existing archive > records, each record in your import source data (in your case your CSV > data) needs to include the existing data for all fields as well as the > updated rainfall data. You then need to use the (as yet undocumented) > --update command line option when running weectl import. > > > So rather than just the records I'm *missing*, I need to go from the > first missing record to the last missing record, inclusive of all > in-between records? I can easily do that, just not 100% on whether it's > required. > > > No, you don't need 'in between records'. Your import data should only > include the records you intend to update/alter, you do need the data from > the other fields of the records you are updating/importing. Perhaps an > example might make it clearer. Say your archive table contains the > following: > > dateTime outTemp inTemp rain outHumidity > 1742000400 23.1 20.1 1.0 80 > 1742000100 23.2 20.1 2.0 81 > 1741999800 23.2 20.1 5.0 84 > 1741999500 23.3 20.2 0.0 83 > 1741999200 23.5 20.2 0.8 83 > 1741998900 23.5 20.2 0.6 84 > 1741998600 23.4 20.1 0.6 82 > 1741998300 23.3 20.1 0.8 83 > 1741998000 23.3 20.2 0.0 84 > 1741997700 23.3 20.1 1.0 82 > > Lets say you want to update the rain value in records timestamped > 1741999500 and 1741998000 (ie the 0.0 values) and your new rain values > (per archive period values) for these two records are 0.2 and 0.4 > respectively. So you might start with your import data as follows: > > dateTime rain > 1741999500 0.2 > 1741998000 0.4 > > If you import this data using the --update command line option (or > without the --update command line option if you deleted the two archive > table records concerned) your archive table will be: > > dateTime outTemp inTemp rain outHumidity > 1742000400 23.1 20.1 1.0 80 > 1742000100 23.2 20.1 2.0 81 > 1741999800 23.2 20.1 5.0 84 > 1741999500 0.2 > 1741999200 23.5 20.2 0.8 83 > 1741998900 23.5 20.2 0.6 84 > 1741998600 23.4 20.1 0.6 82 > 1741998300 23.3 20.1 0.8 83 > 1741998000 0.4 > 1741997700 23.3 20.1 1.0 82 > > Each row in your import data is considered to be an archive record in > itself and any fields that are not included in your import data will be > empty. To keep the rest of the existing data in the records being updated > (in this case outTemp, inTemp and outHumidity), your import data needs to > include values for outTemp, inTemp and outHumidity. Your import data > would be: > > dateTime outTemp inTemp rain outHumidity > 1741999500 23.3 20.2 0.2 83 > 1741998000 23.3 20.2 0.4 84 > > Importing this data will give you: > > dateTime outTemp inTemp rain outHumidity > 1742000400 23.1 20.1 1.0 80 > 1742000100 23.2 20.1 2.0 81 > 1741999800 23.2 20.1 5.0 84 > 1741999500 23.3 20.2 0.2 83 > 1741999200 23.5 20.2 0.8 83 > 1741998900 23.5 20.2 0.6 84 > 1741998600 23.4 20.1 0.6 82 > 1741998300 23.3 20.1 0.8 83 > 1741998000 23.3 20.2 0.4 84 > 1741997700 23.3 20.1 1.0 82 > > Which I suspect is the desired result. Of course this is a very simple and > unrealistic example but hopefully you get the idea. > > > > Alternatively, you could avoid using the --update command line option by > first deleting the records to be imported from the WeeWX database and then > importing your merged/updated CSV data. > > > If you'll save me the research, how do I delete specific records/periods, > either a single period record, or a range? I just recently noticed that the > soilMoist1 observation, on my weewx testing install, recorded about 84 > hours of very wrong values, that my production weewx install didn't (both > getting the data from the same mqtt topic). I know how to drop entire > observation columns via weectl database drop-columns NAME, but in this > case it's just range of periods within the column that need purging. Or is > it as simple as, zeroing out the values in a range via sqlite> update > archive set soilMoist1=0.0 where dateTime > {startepochtime} and dateTime > <= {endepochtime]; ? > > > > In terms of deleting records I use the sqlite3 utility to allow me to > use SQL commands to interact with the database. Some people use GUI based > sql editors to manipulate the database; I don't so I can't help you there. > In terms of sqlite3 the following commands can be used to delete archive > table records: > > $ sqlite3 /var/lib/weewx/weewx.sdb > sqlite> DELETE FROM archive WHERE dateTime=1741999500; > sqlite3> .q > $ > > In this case we deleted the archive record timestamped 1741999500. > > To delete a range of archive records by timestamp use something like: > > sqlite> DELETE FROM archive WHERE dateTime>=1741969500 AND > dateTime<=1741999500; > > which would delete all archive records timestamped from 1741969500 > to 1741999500 inclusive. > > Note that you may need to install the sqlite3 utility on your system using > something like (for debian based systems): > > $ sudo apt install sqlite3 > > -- > You received this message because you are subscribed to a topic in the > Google Groups "weewx-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/weewx-user/1KX3-8lbU34/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/weewx-user/4506f570-fb2c-40f2-95ab-a98c02a59011n%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/4506f570-fb2c-40f2-95ab-a98c02a59011n%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 visit https://groups.google.com/d/msgid/weewx-user/CAH3iRs3ywaiTjLMeJbezwSXjQqXe1B8ysRtv_v4wqg4WLsrPBA%40mail.gmail.com.
