oops - typo'd the field names in multiple places previously. Sorry. I was working off an alternate db with different field names than weewx. This one matches the weewx schema.
select dateTime,datetime(dateTime,'unixepoch','localtime') from archive where datetime(dateTime,'unixepoch','localtime') like "2024-12-28 09%" limit 2; 1735405200|2024-12-28 09:00:00 1735405500|2024-12-28 09:05:00 On Wednesday, March 19, 2025 at 12:23:53 PM UTC-7 vince wrote: > Ryan - here's an example. It looks for the first two records from > 2024-12-18 starting with 9am localtime. > > select datetime,datetime(datetime,'unixepoch','localtime') from archive > where datetime(datetime,'unixepoch','localtime') like "2024-12-28 09%" > limit 2; > 1735405200|2024-12-28 09:00:00 > 1735405500|2024-12-28 09:05:00 > > On Tuesday, March 18, 2025 at 9:16:49 PM UTC-7 vince wrote: > >> >> … where datetime(dateTime,'unixepoch','localtime') … gets you a more >> human friendly datetime but you’ll have to work out the syntax for >> comparison to a date string that does what you want. >> >> Still not understanding why you’re working so hard on this since you know >> which days your network was down. Heck you can pretty close visually just >> by looking at the graphs. But see if the syntax above helps any. >> >> Away from computer so I can’t give you a complete example… >> >> On Tuesday, March 18, 2025 at 8:05:50 PM UTC-7 Ryan Stasel wrote: >> >>> 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/7df7a8ff-e956-42d1-9afc-18efe11699adn%40googlegroups.com.
