Cropping days from weewx database

[miso k]

Hello,

succesfully running my weewx (with belchertown skin and several servers to uploads)

problem is, that i have started my weewx server few days earlier, as I have connected my PWS. 

my server started 12.2.2020, PWS started 18.2.2020.

weewx filled the days before PWS started with own data. 

Q: how to delete days from 12.2. to 18.2.2020?

thanks

(RPi 3B installation)

Michal, SVK

[gjr80]

Hi,

To delete all data over some time period will require you to use a little SQL to manipulate your database. Assuming you are using SQLite and not MySQL/MariaDB the following should do the job:

1. Install the sqlite3 package if not already installed:

$ sudo apt-get update

$ sudo apt-get install sqlite3

2. Stop WeeWX:

$ sudo /etc/init.d/weewx stop

3. Make a backup of your database file weewx.sdb which you will find in /home/weewx/archive or /var/lib/weewx depending on your install type (assuming /home/weewx/archive):

$ cp /home/weewx/archive/weewx.sdb /home/weewx/archive/weewx_backup.sdb

4. Open your database using sqlite3:

$ sqlite3 /home/weewx/archive/weewx.sdb

sqlite> 

5. Carefully select the period over which you wish to delete the data, let's say you want to delete all records from midnight at the start of 25 February 2020 through until 04:30am 28 February 2020 inclusive. In that case you would use the following command to delete the data:

sqlite> DELETE FROM archive WHERE datetime(dateTime,'unixepoch','localtime')>='2020-02-25 00:00:00' AND datetime(dateTime,'unixepoch','localtime')<='2020-02-28 04:30:00';

To delete all data earlier than a particular date-time use something like:

sqlite> DELETE FROM archive WHERE datetime(dateTime,'unixepoch','localtime')<='2020-02-28 04:30:00';

in your case:

sqlite> DELETE FROM archive WHERE datetime(dateTime,'unixepoch','localtime')<'2020-02-18 00:00:00';

would delete all data earlier than midnight at the start of 18 February 2020.

You can check if the data has been deleted by entering the following command (change dates and times in quotes as required):

sqlite> SELECT dateTime, datetime(dateTime,'unixepoch','localtime') FROM archive WHERE datetime(dateTime,'unixepoch','localtime')>='2020-02-25 00:00:00' AND datetime(dateTime,'unixepoch','localtime')<='2020-02-28 04:30:00';

or

sqlite> SELECT dateTime, datetime(dateTime,'unixepoch','localtime') FROM archive WHERE datetime(dateTime,'unixepoch','localtime')<'2020-02-18 00:00:00';

You should see no data returned.

6. Exit sqlite:

sqlite> .q

7. There is one more step involved and that is to rebuild the daily summaries for the period over which you deleted the data. For this we use the WeeWX utility wee_database. wee_database can rebuild the daily summaries over all dates or we can limit the rebuild to just the dates affected. wee_database works with dates and not time so if you only deleted a few hours of data on a given day you need to rebuild the whole day. Assuming the above where we deleted data from 25 to 28 February 2020 use the following command to rebuild the affected daily summaries (change dates to match the data you deleted):

$ wee_database -rebuild-daily --from=2020-02-25 --to=2020-02-28

8. That is it, you can restart WeeWX.

$ sudo /etc/init.d/weewx start

9. If you have any plots that use the old deleted data you may have to wait until they are all regenerated (can take up to 24 hours) for the old data to disappear. Alternatively you can just delete all of the generated image files and that will force WeeWX to re-generate them all on the next report cycle. Unless you have reports that are generated infrequently all of your reports should reflect the changed data after the next report cycle.

If you accidentally deleted the wrong data or otherwise made a mistake you can revert to your old database by copy weewx_backup.sdb back over weewx.sdb and then start again from step 4.

Gary

[miso k]

thank you, it worked!

the sqlite commands need to be splitted at separed lines, but it works! 

now my database is clean. everyday better :)

Michal

Dňa streda, 4. marca 2020 15:59:34 UTC+1 miso k napísal(a):