I'm drawing a blank here... I'm fine altering my import data to not include overlapping data... but how the heck do I figure out what's missing?
While I can convert unixepoch to a readable date, is there an easy way to just do something like select * from archive where datetime -like "2025-02-28"; Then I can see what's missing and alter the import file. Thanks! On Tuesday, March 18, 2025 at 5:11:07 AM UTC-7 [email protected] wrote: > Attached is a simple sqlite query for finding extra database rows. In it, > both databases are weewx databases, so if you're trying to find differences > between weewx and some other database, you'll have to modify the query, but > the idea remains the same. > > Note that the order of the databases is important. It reports rows that > are IN db1 but NOT IN db2. If you swapped the order of the databases, the > counts would still be correct but it would print no rows because every > record in the smaller database is in the larger database. > > Walt > On Monday, March 17, 2025 at 5:07:39 PM UTC-5 gjr80 wrote: > >> If you want to tell what data already exists you will need to get your >> SQL hands dirty (you could use Cheetah produce a WeeWX report that lists >> all the timestamps in some given period, but that's an awful lot of messing >> around for little gain). WeeWX archive record timestamps are saved as an >> integers, with the DBMS enforcing the unique constraint. So a source record >> with one second difference to an existing record would be imported. >> >> The import process does report on what records were imported and what >> records were not via the log; imported records will be logged as being >> added and records that were rejected due to duplicate timestamps will be >> reported with a unique key error. Arguably a little crude, but to do any >> more detailed user friendly analysis and reporting would have to expensive >> time wise. Of course the reporting is after the fact not before. >> >> Gary >> >> On Tuesday, 18 March 2025 at 02:14:24 UTC+10 [email protected] wrote: >> >>> Thanks gjr80. I guess the question is, how do I tell easily what data >>> already exists (the time stamps will have to match, I assume). So >>> "2025-02-28 08:15:00" existing (for example) I assume won't match against >>> an import of "2025-02-28 08:16:00" (let alone if there's seconds other than >>> 00). >>> >>> Is there an "easy" way to tell? or am I stuck going into sqlite3 and >>> doing some selects (not hard, just was kind of hoping the import process >>> WOULD report on records already existing). =) >>> >>> On Sunday, March 16, 2025 at 5:59:03 PM UTC-7 gjr80 wrote: >>> >>>> The default weectl import action is to *not* import records where >>>> there is already an archive record in the database with the same >>>> timestamp. weectl >>>> import will appear to import such records but if you look at the WeeWX >>>> log you will see entries similar to: >>>> >>>> 2023-11-04 15:33:01 weectl-import[3795]: ERROR weewx.manager: Unable to >>>> add record 2018-09-04 04:20:00 AEST (1535998800) to database 'weewx.sdb': >>>> UNIQUE >>>> constraint failed: archive.dateTime >>>> >>>> indicating a record with the same timestamp (in this case 2018-09-04 >>>> 04:20:00 AEST (1535998800)) already exists in the database and the >>>> imported >>>> record was discarded. Long story, but it was too inefficient to check >>>> every >>>> record before attempting to add it to the archive, hence the somewhat >>>> cumbersome import and notification. Note that if you use the (presently) >>>> undocumented --update command line option with weectl import >>>> pre-existing records will be overwritten. >>>> >>>> Bottom line - just run weectl import as per the docs, and whatever you >>>> do don't include --update on your weectl import command line. And of >>>> course always make a backup of your database before importing. >>>> >>>> Gary >>>> On Monday, 17 March 2025 at 09:56:17 UTC+10 [email protected] wrote: >>>> >>>>> I lost a few days of data due to a network issue, but I have the data >>>>> in another system. Am trying to import from csv, but I think my csv >>>>> overlaps a bit with what's already in the weewx database. >>>>> >>>>> Doing a dryrun, it says it will import every line in the csv... >>>>> >>>>> Is there a way to get weectl import to NOT import duplicate data, or >>>>> is there an easy way to see what data is in those dates so I can modify >>>>> csv >>>>> to exclude those entries? >>>>> >>>>> Thanks! >>>>> >>>> -- 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/aee69a29-691f-4559-98f3-591ef17ac9f6n%40googlegroups.com.
