Thanks a lot, everything worked I did something between, what means i shrinked database with has data including missing day to only this day. While this I learned that filesize remains until I use "VACUUM" to deflate file. File after this operation is only 2.3MB instead of somethinge like 400MB ==> name here weews_1day-of-data.sdb
Than I followed Tom's advice. * sqlite3 /var/lib/weewx/weewx_1day-of-data.sdb* sqlite>* .output /var/tmp/weewx_1day-of-data.sql* sqlite>* .dump* sqlite>* .quit* * Copied to my new RPI and did* * sudo cp /var/lib/weewx/weewx.sdb /var/lib/weewx/weewx.sdb.backup* # Merge: * sudo sqlite3 /var/lib/weewx/weewx.sdb < /var/tmp/weewx_1dy-of-data.sql* And it worked at first try. So again, thanks a lot. Dirk [email protected] schrieb am Samstag, 5. Oktober 2024 um 15:33:32 UTC+2: > I didn't test this but using sqlite3 you should be able to do something > like: > > attach "/var/lib/weewx/weewx.sdb" as current; > attach "/var/lib/weewx/old.sdb" as old; > insert into current.archive select * from old.archive where date(datetime, > 'unixepoch', 'localtime') == '2024-03-10'; > > I would copy both databases and use those copies in the statements above > so you don't risk messing up the > originals. > > Don't supply a file name on the command line, just start sqlite3, it will > say "connected to a transient in-memory database". > Then execute the sql statements above, supplying the correct filenames in > the ATTACH statements. > You will have to rebuild or update the dailies afterwards. > > Walt > > On Saturday, October 5, 2024 at 1:48:46 AM UTC-5 Dirk Uhlich wrote: > >> Hi Tom, >> >> thanks. You are right, I intalled using apt, so my databases are located >> in /var/lib/weewx >> >> I manaeged to save data since beginning of my runnging system since the >> 03.10.2024 ==> lets name it weewx.sdb-2024 >> On my second pi with weatherstick I was doing some tests and tried to >> make a display with LCD ==> data starting some day in past but pi is still >> running ==> lets name weewx.weather (here I'll find my missing day) >> >> Now my goal was to use all data out of weewx.sdb-2024 adding only one day >> of weewx.weather (ignoring all data before 03.10.2024 (and now also all >> data starting 05.10.2024)) >> >> Maybe also merging two complete databases could work, but im sure, there >> will be more than one year of duplicates - if this will work, I'll give it >> a try. >> >> Is it possible to cut away all data before or starting a given timestamp? >> TThis could reduce errors? >> >> Just to ask, will database after merging be repaired in backgroud or why >> it's possible to ignore all errors? >> >> Thanks a lot and have a nice weekend. >> Dirk >> >> Tom Keffer schrieb am Freitag, 4. Oktober 2024 um 22:39:45 UTC+2: >> >>> You did not say how you did the install, but I'll assume you used the >>> package installer with apt. This means your database is located at >>> /var/lib/weewx/weewx.sdb. Adjust as necessary. >>> >>> On the machine that has the one day of data, dump the database into a >>> text file: >>> >>> *sqlite3 /var/lib/weewx/weewx.sdb* >>> sqlite>* .output /var/tmp/weewx.sql* >>> sqlite>* .dump* >>> sqlite>* .quit* >>> >>> Copy the file /var/tmp/weewx.sql to the new machine somehow, perhaps on >>> a USB stick. I'll assume you put it in /var/tmp/weewx.sql on the new >>> machine. >>> >>> On the new machine: >>> >>> # Make a backup: >>> *sudo cp /var/lib/weewx/weewx.sdb /var/lib/weewx/weewx.sdb.backup* >>> # Merge: >>> *sudo sqlite3 /var/lib/weewx/weewx.sdb < /var/tmp/weewx.sql* >>> >>> You will get many errors, including that the "table archive already >>> exists", and "UNIQUE constraint failed". You can ignore these. >>> >>> -tk >>> >>> >>> On Fri, Oct 4, 2024 at 11:42 AM Dirk Uhlich <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> i've searched some time, but no luck or wrong search method. >>>> At my weather station Vantage Pro2 with Raspberry the USB-Stick died or >>>> is dying. It stoped working but I was able to save some data. >>>> >>>> I managed to install everything on a new USB but now data of one day is >>>> missing. >>>> Running second pi with weatherstick has this data but here's my problem: >>>> >>>> How do I manage to copy one day of Data (Timestamp 1728000600 to >>>> something like 1728069000) from one weex.sdb to my new one with >>>> missing data. >>>> I know stopping weewx is mandatory, but I've no experience with sqlite. >>>> >>>> Important to know:actual weewx.sdb contains data sind 2021 until >>>> yesterday ... >>>> >>>> Maybe anybody could help. >>>> >>>> Thanks a lot >>>> Dirk >>>> >>>> -- >>>> 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 on the web visit >>>> https://groups.google.com/d/msgid/weewx-user/307fed91-f842-4a79-99d2-7de6f34f5336n%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/weewx-user/307fed91-f842-4a79-99d2-7de6f34f5336n%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 on the web visit https://groups.google.com/d/msgid/weewx-user/2e47e044-cc00-4d9c-8b09-29e0fd214272n%40googlegroups.com.
