How can I edit Sqlite databe ?

[Sebastien Lhoir]

Hello,

When I Installed my Lacross WS28xx station, I made a mistake. I set the date 2017 instead of 2016.

In April of this year, I saw this mistake and I modified the date on the station.

Now, I have installed weewx on a rapsberry  to collect data. It's works fine !

My problem is : datas between 07/2015 and 04/2016 have a bad date.

How can I modifiy ?

Do I have to correct data in sqlite database ? If yes, how can I do ? Is there an editor ? What's the format of the db ? Do I have to stop weewx to do manipulation ?

Do I also have to erase all data from my station (and how ?).

Thank you for your help !

[Sebastien Lhoir]

I started to download sqlite browser on windows platform.

weewv.sdb has a lot of tables : 52 tables :-(

Many of them have a datetime field. I think only 1 don't have a datetime field :-( :-(

Do you known what of them I have to modify ? I hope not all, but...

All "Datetime" field are integer ? How can I take off 1 year ? And only for record  after today...

For All table :

update table xxxx

set dateTime = dateTime - "How Much"

where dateTime > "How Much value for today"

Waht is the good way to do ? 

1. Stop weewx

2. Re-initialize my station to erase all datas

3. update my db

4. start Weewx

Thanks for any help....

[Sebastien LHOIR]

I answer to myself

update archive set dateTime = dateTime-31536000 where dateTime>strftime('%s','now')

31536000 is the number of second in one year of 365 days

Now, I need to known what tables have to be updated and if this is the good way to do

1. Stop weewx

2. Re-initialize my station to erase all datas

3. update my db from sqlite browser + reindex

4. start Weewx

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/A567olphB8w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gjr80]

Hi,

You only need to fix dateTime in the table archive, the remaining archive_day_xxxxxx tables just hold the daily summaries (you could try fixing them but there are way too many of them and they would need more than just the dateTime fields being adjusted). A far better approach is to fix your archive table then drop and rebuild the daily summaries using the wee_database utility. Dropping/rebuilding the daily summaries is explained here.

Also, a word of caution when using graphical editors to edit SQLite databases, it is very easy to unintentionally introduce spaces into fields that are null (None or empty) or change numeric fields to strings. These sorts of things can cause all sorts of weird errors. Learning a bit of basic SQL(ite) and using sqlite3 from the command line will be a wise investment in the future.

Gary

[Sebastien LHOIR]

Thank you Gary.

I'm sorry but this doesn't work .

I did those command and after I always a bad date...

pi@raspberrypi:/var/lib/weewx $ ls -al

total 1264

drwxr-xr-x  2 root root    4096 jun 27 16:29 .

drwxr-xr-x 51 root root    4096 jun 27 13:43 ..

-rw-r--r--  1 root root 1283072 jun 27 16:29 weewx.sdb

I stopped services weewx and apache 

pi@raspberrypi:/var/lib/weewx $ sudo /etc/init.d/weewx stop

[ ok ] Stopping weewx (via systemctl): weewx.service.

pi@raspberrypi:/var/lib/weewx $ sudo /etc/init.d/apache2 stop

[ ok ] Stopping apache2 (via systemctl): apache2.service.

I made a copy of the db.

pi@raspberrypi:/var/lib/weewx $ sudo cp weewx.sdb weewx.sdb.org

pi@raspberrypi:/var/lib/weewx $ ls -al

total 2520

drwxr-xr-x  2 root root    4096 jun 27 16:31 .

drwxr-xr-x 51 root root    4096 jun 27 13:43 ..

-rw-r--r--  1 root root 1283072 jun 27 16:29 weewx.sdb

-rw-r--r--  1 root root 1283072 jun 27 16:31 weewx.sdb.org

I modified db from sqlite3 command line

pi@raspberrypi:/var/lib/weewx $ sudo sqlite3 weewx.sdb 

SQLite version 3.8.7.1 2014-10-29 13:59:56

Enter ".help" for usage hints.

sqlite> update archive set dateTime=dateTime-31536000 where dateTime>strftime('%s', 'now');

sqlite> .quit

I reindex database

pi@raspberrypi:/var/lib/weewx $ sudo wee_database /etc/weewx/weewx.conf --drop-daily

Using configuration file /etc/weewx/weewx.conf

Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

Proceeding will delete all your daily summaries from database 'weewx.sdb'

Are you sure you want to proceed (y/n)? y

Dropping daily summary tables from 'weewx.sdb' ... 

Dropped daily summary tables from database 'weewx.sdb'

pi@raspberrypi:/var/lib/weewx $ sudo wee_database /etc/weewx/weewx.conf --backfill-daily

Using configuration file /etc/weewx/weewx.conf

Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

Backfilling daily summaries in database 'weewx.sdb' ...

Processed 3786 records to backfill 157 day summaries in 13.23 seconds      

Backfill of daily summaries in database 'weewx.sdb' complete

I restarted services

pi@raspberrypi:/var/lib/weewx $ sudo /etc/init.d/weewx start

[ ok ] Starting weewx (via systemctl): weewx.service.

pi@raspberrypi:/var/lib/weewx $ sudo /etc/init.d/apache2 start

[ ok ] Starting apache2 (via systemctl): apache2.service.

pi@raspberrypi:/var/lib/weewx $ 

From my navigator, on http://localhost/weewx/ , I always have "

Current Weather Conditions

16/04/17 08:00:00

Thank you

Sebastien

--

[Andrew Milner]

Thew web pages will not be updated until the first archive interval has passed following the restart.  Either wait, or run wee_reports directly instead of starting weewx

or delete existing web pages before restarting weewx

I am sure you are seeing the web page with the previous database contents before the update was applied!!!!

.

[Sebastien Lhoir]

That's right.
One hour later, web pages are updated

I'm happy !!!!

Thank you everyone !!!