Tom, Thanks so much for posting this information. I was looking for a way to merge all these orphan .sdb files that I have due to the frequent date reset issue that I have and posted about earlier. The result was several sdb files that had 2-3 weeks of data in them.
I just tested this solution and I am happy to report that it worked perfectly. I first stopped the service so the current .sdb file would not be modified. Then I made copies of the files in another directory and did the two commands as my regular user so I didn't have to 'sudo'. Finally I cp'd over the new .sdb overwriting the weewx.sdb file and then restarted the service. Lastly, I verified in the new images that the time frame of my database suddenly went back an additional 2 weeks. I'm not sure about the error but I kind of suspect maybe there was a duplicate entry in the database. But it did not prevent the actual merge from working. Thanks so much for this solution Ken Mesa AZ Below is some of the relevant output: ken@WeewxWeatherServer:~/w/try$ ll total 13872 -rw-r--r-- 1 ken ken 3923968 Apr 24 11:35 weewx.sdb -rw-r--r-- 1 ken ken 2289664 Apr 24 11:31 weewx.sdb_202004090905 ken@WeewxWeatherServer:~/w/try$ echo ".dump archive" | sqlite3 weewx.sdb_202004090905 | grep -v 'CREATE TABLE' > file1.sql ken@WeewxWeatherServer:~/w/try$ sqlite3 weewx.sdb < file1.sql Error: near line 11483: UNIQUE constraint failed: archive.dateTime ken@WeewxWeatherServer:~/w/try$ ll -rw-rw-r-- 1 ken ken 5210741 Apr 24 11:36 file1.sql -rw-r--r-- 1 ken ken 5791744 Apr 24 11:36 weewx.sdb -rw-r--r-- 1 ken ken 2289664 Apr 24 11:31 weewx.sdb_202004090905 ken@WeewxWeatherServer:~/w/try$ ken@WeewxWeatherServer:~/w$ sudo ./start [ ok ] Starting weewx (via systemctl): weewx.service. Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Debug is 1 Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Initializing engine Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: engine: Loading station type Vantage (weewx.drivers.vantage) Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: vantage: Driver version is 3.1.1 Apr 24 11:39:26 WeewxWeatherServer systemd[1]: Started LSB: weewx weather system. Apr 24 11:39:26 WeewxWeatherServer weewx[5467]: vantage: Opened up serial port /dev/ttyS0; baud 19200; timeout 4.00 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Retry #0 failed Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Hardware type is 16 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: ISS ID is 1 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Hardware name: Vantage Pro2 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdTimeSynch Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdTimeSynch Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdConvert Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: StdConvert target unit is 0x1 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdConvert Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdCalibrate Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdCalibrate Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdQC Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.engine.StdQC Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.wxservices.StdWXCalculate Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Finished loading service weewx.wxservices.StdWXCalculate Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Loading service weewx.engine.StdArchive Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Archive will use data binding wx_binding Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: The archive interval in the configuration file (300) does not match the station hardware interval (60). Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Using archive interval of 60 seconds (specified by hardware) Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Using binding 'wx_binding' to database 'weewx.sdb' Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: manager: Starting backfill of daily summaries Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Starting up weewx version 3.9.2 Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: engine: Clock error is 1.44 seconds (positive is fast) Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Getting archive packets since 2020-04-24 11:28:00 MST (1587752880) Apr 24 11:39:30 WeewxWeatherServer weewx[5467]: vantage: Gentle wake up of console successful On Fri, Apr 24, 2020 at 9:42 AM B Major <major...@gmail.com> wrote: > First off, let me thank you for your assistance and direction here. You > pointed me to the sqlite utilities which is what I was wondering; can I use > them here. I am taking the time to learn more about sqlite database. > Second off, thank you for kindly answering when I clearly didn't use the > group search function effectively. There is a lot of useful information on > this group. > Thanks for sharing the nice weewx application too. > > Brian > > > On Friday, April 24, 2020 at 4:21:11 AM UTC-7, Tom Keffer wrote: >> >> I seem to recall there was a thread on the forum about this not too long >> ago. >> >> The simplest would be to dump one file, using the tools sqlite3, then >> import into the other, again using sqlite3. Something like (NOT TESTED): >> >> echo ".dump archive" | sqlite3 file1.sdb | grep -v 'CREATE TABLE' >> >file1.sql >> sqlite3 file2.sdb < file1.sql >> >> where file1.sdb is the sqlite database you want to add to file2.sdb. >> >> The first line dumps file1.sdb, removing the 'CREATE TABLE' statement >> (you don't want it because you already have the table in file2.sdb). It >> saves the results to file1.sql, which will be full of SQL "INSERT" >> statements. >> >> The second line then uses that file to insert the new data into file2.sdb >> . >> >> Needless to say, make backups first. >> >> -tk >> >> >> >> >> >> >> On Thu, Apr 23, 2020 at 8:59 PM B Major <maj...@gmail.com> wrote: >> >>> I would like to merge 2 weewx SQLite databases. I am currently planning >>> to use the wee_import function. Is there a better way to do this? >>> >>> The back story here is that I recently got a new desktop. I installed >>> the latest version of weewx and had it running in short order. I have my >>> old weewx SQLite database. I'd like to keep all that data. As noted, I was >>> going to just use wee_import to the new database after extracting the >>> records from the old one. But given that it appears that these are of the >>> same format is there an easier way to do this? I've been running the new >>> system for a couple of weeks so swapping out databases may be an option but >>> I'd like to keep those 2 weeks of data. These files are Linux based. >>> >>> Any suggestions or ideas for ease of effort would be appreciated. >>> >>> >>> -- >>> 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...@googlegroups.com. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/weewx-user/f227d9ab-78ed-41b0-a7ee-75a1114dcb8e%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/f227d9ab-78ed-41b0-a7ee-75a1114dcb8e%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 weewx-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-user/e75a8e17-b0bc-46a5-9d4e-7236921e2bd0%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/e75a8e17-b0bc-46a5-9d4e-7236921e2bd0%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 weewx-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CACZLRFrjheMo6i4O55B6L_BDGFE6sTY8rBOM_rhtPZhPgrN3tw%40mail.gmail.com.
