Removing erroneous rain readings from the database

[Dana Roode]

Hello,

I've had some issues with the rain collector on my Vantage Pro2 this year, I replaced it a few weeks back.  There were some spurious high readings, plus my testing generated false readings.  The data indicates over 60 inches of rain in my desert location this year.  I'd like to find the bad values and zero them out, and I see how to do this with sqlite3 on the database if I understood the data fields.  "rain" is the field in the archive tables, but I don't know what time period thats for.  The dateTime field has numbers like 1539650100 and I don't know how to convert to something meanful.

Is there documentation on the database schema and fields somewhere?  I haven't found it in the wiki information.  

Any pointers would be appreciated.

  Dana Roode

[Greg Troxel]

Dana Roode <dana....@gmail.com> writes:

> Hello,
>
> I've had some issues with the rain collector on my Vantage Pro2 this year,
> I replaced it a few weeks back. There were some spurious high readings,
> plus my testing generated false readings. The data indicates over 60
> inches of rain in my desert location this year. I'd like to find the bad
> values and zero them out, and I see how to do this with sqlite3 on the
> database if I understood the data fields. "rain" is the field in the
> archive tables, but I don't know what time period thats for. The dateTime
> field has numbers like 1539650100 and I don't know how to convert to
> something meanful.

I would guess that rain is either the rain that arrived in that archive
interval, or some sort of cumulative value.

The dateTime is in seconds since the Unix epoch, which was
1970-01-01T00:00:00Z (Jan 1, 1970, midnight UTC).

On Unix, date -r will take that and convert it to human time. This is
just what is does normally, except it uses the provided value vs
gettimeofday().

$ date -r 1539650100
Mon Oct 15 20:35:00 EDT 2018

which isn't a few weeks ago.

On windows, you could install Unix and then run date -r. Probably
there's an easier way...

[Dana Roode]

Thanks Greg!  I am running on Unix, so this is very helpful.   Dana

On Sunday, December 22, 2019 at 1:55:16 PM UTC-8, Greg Troxel wrote:

[Thomas Keffer]

The database schema is described in the section The database in the Customizing Guide. It also describes the field dateTime.

The field rain is the amount of rain that fell in that archive record. For an archive interval of, say, 5 minutes, this would be the amount of rain that fell during that 5 minutes.

Also, see the Wiki article Cleaning up old 'bad' data. It may help.

-tk

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/f853d32c-80ce-4114-80a3-7313eb32234a%40googlegroups.com.

[Graham Eddy]

from sqlite3 use datetime function to display dateTime field in more meaningful way

(names ‘datetime’ function in sqlite3 and ‘dateTime’ field in weewx database being so similar is coincidence..)

example showing the rain for the last entry (i.e. maximum dateTime) in weewx database

  $ sqlite3 weewx.sdb

  sqlite> select datetime(max(dateTime), "unixepoch”, "localtime"), rain from archive;

____________

Graham Eddy

[Dana Roode]

Thanks for the pointers everyone.  The datetime function comes in handy, and I appreciate the schema and other references.  I have cleared out the bad data, although I followed an example and set the value to NULL, perhaps I should have gone with 0.  Now when I try  to find the NULL values (where rain=NULL) I do not get any hits, but they show up in other queries.  I was thinking about setting them to 0.

Most of the reports look better now.  The annual report is still showing a peak rain rate for the data I deleted, I'm not sure where that comes from.  I deleted the daily summaries and they have been recreated.

 

Its raining here in Southern California, second set of storms for the season.  I don't get much opportunity to test the rain collector but it appears to be working.

  Dana