Yay! Reminds me that the "cleanup up bad data" article really needs an example that deals with time. -tk
On Fri, Feb 7, 2020 at 2:43 PM Troy Lass <[email protected]> wrote: > Ok, that helps, didn't think to read the other examples - thanks. > > I did the following.... > > Below the first query shows the last good record and the next 2 bad > records. > root@deadduck:/var/tmp# echo "SELECT * FROM archive WHERE (dateTime >= > 1566143400);" | sqlite3 /var/tmp/weewx.sdb > > 1566143400|1|5|30.4558189104303|30.4558189104303|37.7623351681816|71.733842431388|69.5849521883363|12.532315137224|60.6745673129079|3.22090544784868|244.044984074789|3.8954353891475|243.136938325575|0.0|0.0|55.3830091077274|69.5849521883363|69.5849521883363|0.00120036639468108|701.324093624923|9.81853731074892||||||||||||||||||19.3618624420658|0.0|11.9735444627246||||11.9386770269783|12.0367022469557|11.9772054338739|0.0|0.0|0.183333333333333|0.0 > > 1579220400|1|10|30.215|||69.7|28.3|20.0|51.0|0.0||2.0|67.5|0.0|0.0|12.5154339623535|28.3|28.3|0.0||||||||||||||||||||64.0625|||||||||||| > > 1592324580|1|30|27.704|||||9.0|||3757.5|160.0|5557.5|8.14|0.0||||0.0||14.7||29.0|148.0|69.0||163.0|129.0|93.0|93.0|61.0|253.0|253.0|251.0|253.0|253.0|95.0|249.0|100.0|||||||||||| > > Next I deleted the 2 bad rows... > root@deadduck:/var/tmp# echo "delete from archive where (dateTime >= > 1579220400) limit 2;" | sqlite3 /var/tmp/weewx.sdb > > The first query again shows the bad records are gone - whew... > root@deadduck:/var/tmp# echo "SELECT * FROM archive WHERE (dateTime >= > 1566143400);" | sqlite3 /var/tmp/weewx.sdb > > 1566143400|1|5|30.4558189104303|30.4558189104303|37.7623351681816|71.733842431388|69.5849521883363|12.532315137224|60.6745673129079|3.22090544784868|244.044984074789|3.8954353891475|243.136938325575|0.0|0.0|55.3830091077274|69.5849521883363|69.5849521883363|0.00120036639468108|701.324093624923|9.81853731074892||||||||||||||||||19.3618624420658|0.0|11.9735444627246||||11.9386770269783|12.0367022469557|11.9772054338739|0.0|0.0|0.183333333333333|0.0 > > Next I did the following > cp /var/lib/weewx.sdb /var/lib/weewx.sdb.020720 > cp /var/tmp/weewx/sdb /var/lib/weewx.sdb > wee_database weewx.conf --drop-daily > wee_database weewx.conf --rebuild-daily > > root@deadduck:/var/lib/weewx# 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' ... > Daily summary tables dropped from database 'weewx.sdb' in 0.69 seconds > > root@deadduck:/var/lib/weewx# wee_database /etc/weewx/weewx.conf > --rebuild-daily > Using configuration file /etc/weewx/weewx.conf > Using database binding 'wx_binding', which is bound to database > 'archive_sqlite' > All daily summaries will be rebuilt. > Proceed (y/n)? y > Rebuilding daily summaries in database 'weewx.sdb' ... > Records processed: 389000; Last date: 2019-08-15 16:00:00 MDT (1565906400) > Processed 389790 records to rebuild 2710 day summaries in 120.91 seconds > > Rebuild of daily summaries in database 'weewx.sdb' complete > > root@deadduck:/var/lib/weewx# wee_device --info > Using configuration file /etc/weewx/weewx.conf > Using Vantage driver version 3.1.1 (weewx.drivers.vantage) > Querying... > Davis Vantage EEPROM settings: > > CONSOLE TYPE: Vantage Pro2 > > CONSOLE FIRMWARE: > Date: Sep 11 2017 > Version: 3.80 > > CONSOLE SETTINGS: > Archive interval: 300 (seconds) > Altitude: 6100 (foot) > Wind cup type: large > Rain bucket type: 0.01 inches > Rain year start: 1 > Onboard time: 2020-02-07 15:53:12 > > CONSOLE DISPLAY UNITS: > Barometer: inHg > Temperature: degree_F > Rain: inch > Wind: mile_per_hour > > CONSOLE STATION INFO: > Latitude (onboard): +40.3° > Longitude (onboard): -105.2° > Use manual or auto DST? AUTO > DST setting: N/A > Use GMT offset or zone code? ZONE_CODE > Time zone code: 5 > GMT offset: N/A > Temperature logging: LAST > Retransmit channel: OFF (0) > > TRANSMITTERS: > Channel Receive Repeater Type > 1 active none (N/A) > 2 active none iss > 3 active none (N/A) > 4 active none (N/A) > 5 active none (N/A) > 6 active none (N/A) > 7 active none (N/A) > 8 active none (N/A) > > RECEPTION STATS: > Total packets received: 482 > Total packets missed: 0 > Number of resynchronizations: 0 > Longest good stretch: 482 > Number of CRC errors: 9 > > BAROMETER CALIBRATION DATA: > Current barometer reading: 29.981 inHg > Altitude: 6100 feet > Dew point: 21 F > Virtual temperature: 26 F > Humidity correction factor: 1.2 > Correction ratio: 1.258 > Correction constant: +0.000 inHg > Gain: 0.000 > Offset: -21.000 > > OFFSETS: > Wind direction: +0 deg > Inside Temperature: +0.0 F > Inside Humidity: +0 % > Outside Temperature: +0.0 F > Outside Humidity: +0 % > > root@deadduck:/var/lib/weewx# wee_device --set-time > Using configuration file /etc/weewx/weewx.conf > Using Vantage driver version 3.1.1 (weewx.drivers.vantage) > Setting time on console... > Current console time is 2020-02-07 15:12:42 MST (1581113562) > > root@deadduck:/var/lib/weewx# wee_device --clear-memory > Using configuration file /etc/weewx/weewx.conf > Using Vantage driver version 3.1.1 (weewx.drivers.vantage) > Proceeding will erase all archive records. > Are you sure you wish to proceed (y/n)? y > Erasing all archive records ... > Archive records erased. > > root@deadduck:/var/lib/weewx# wee_device --start > Using configuration file /etc/weewx/weewx.conf > Using Vantage driver version 3.1.1 (weewx.drivers.vantage) > Starting logger ... > Logger started > root@deadduck:/var/lib/weewx# > > root@deadduck:/etc/init.d# ./weewx start > [ ok ] Starting weewx (via systemctl): weewx.service. > root@deadduck:/etc/init.d# > > > Holy crap, I think it's working again... lol I've got couple updates to > the web page with today's date and wind vectors. This is on the console > right now and I will move back to the envoy later once I give it a day or > so to ensure everything is working correctly. This exercise has also > helped me notice a wireless deadspot on my desk very near where I had my > envoy. It's a lot easier to tell when the console is communicating with > the station than the envoy. I'm only about 20ft away from the station and > can see it out of the window so I didn't think it would be an issue but > there's definitely a dead spot on the desk so I'll keep the envoy where the > console is getting updates now. > > I had 2 bad records in the DB, one on 1/16/20 and another in the future > 6/16/20. After getting rid of those it started working again. I wonder if > the first bad entry was part of the problem all along. I'm betting the > logger got corrupted on 8/18/19, the last valid update and I needed to > clear the mem back then. I've poked at this a couple times but never made > any progress until I reached out and everyone was very helpful and timely. > > Thanks everyone for the help with this, I learned a lot about this WeeWX > and like it even more. > > Great support everyone! > > Thanks again! > Troy > > On a side note - as for the wind look at the peak gusts for 2011, it was a > pretty bad year up here. Peak gust was 142, my next door neighbor has a > davis weather station too and he clocked 143 also so I'd say that was > pretty close. I live on the top of a ridge in Colorado and it's beautiful, > unless it's windy.... :-) > > http://deadduck.net/weather/tabular.html?report=NOAA/NOAA-2011.txt > > > > > > On Fri, Feb 7, 2020 at 1:22 PM vince <[email protected]> wrote: > >> On Friday, February 7, 2020 at 12:15:14 PM UTC-8, Troy Lass wrote: >> >>> How do I query the epoc date though? I don't know the column name to do >>> the query on. >>> >> >> Look at the schema. It's called dateTime. >> >> I have some examples at >> https://github.com/vinceskahan/weewx-odds-and-ends/blob/master/fix-bad-rain.md >> if that helps any. >> >> -- >> 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/ic4IK_-eX4Q/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/weewx-user/32200cdd-45e6-4620-a70f-f21896b2a78e%40googlegroups.com >> <https://groups.google.com/d/msgid/weewx-user/32200cdd-45e6-4620-a70f-f21896b2a78e%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/CALBrUnHSnSyD5nB7aDvcELVzOxHUiPE0hkRMqT69Mv1h1L87VQ%40mail.gmail.com > <https://groups.google.com/d/msgid/weewx-user/CALBrUnHSnSyD5nB7aDvcELVzOxHUiPE0hkRMqT69Mv1h1L87VQ%40mail.gmail.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/CAPq0zEBb-aFUQYT2-iTAWqjdHQn-N68Z7F9ZRVj9O0qebHCaaw%40mail.gmail.com.
