Rich,
> Not in the area right now but later this evening will check out the
> datebase.
>
One good thing about importing historical data, it's not going anywhere so
you can take your time to get it right.
SELECT COUNT(dateTime) FROM archive WHERE
> dateTime>=strftime('%s','2017-09-01 00:00:00', 'utc') AND
> dateTime<strftime('%s','2017-10-01 00:00:00', 'utc')
> My Record Count : 8377
> Your Record Count : 8377
>
Given the numbers agree and its short of a full 30 day months data it's a
good indicator the data may be the same.
SELECT outTemp, outHumidity, dewpoint FROM archive WHERE
> dateTime=strftime('%s','2017-09-20 13:55:00', 'utc');
> My Record: 82.2|55.0|64.4
> Your Record: 82.5|55.0|64.4
>
Apologies, typo on my behalf, teach me to copy and paste and not type.
I look at about two dozen times in the area where I got the "UNIQUE
> constraint failed: archive.dateTime" error and the database record matches
> the log file.
>
I think given this and the previous two checks it's pretty safe to say that
your Sep 17 data is in your archive correctly. When you ran wee_import the
last time for Sep 17 somehow some of your data had alredy been imported, we
probably won't know how that happened but if you have some spare hours you
could scour the logs, wee_import should have logged any imports. As for the
other months you would have to say it is quite possibly the same for them.
I guess it it is upto you if you want check them or delete and re-import.
Can you provide me the SQL to delete the Month worth of data. My thinking
> is to delete Sept 17 records and re-import that data or should I delete the
> "oldest Month" first to see what happens.
>
Up to you. I think given what we have found, and since your Cumulus data is
not going anywhere, I would just delete all of the imported Cumulus data
from the archive, place all the Cumulus monthly logs in a folder and run
wee_import on them again. Should be no errors this time, the only price you
will pay is maybe a bit of your time as you wait for the import to run.
As for the SQL to delete, might be a roundabout way but I would do
something like this to delete all of the records that will have been
imported from the Sep17 monthly log. First check my WHERE satement is doing
what I want by using it in a non-destructive SELECT query (I always do
this to make sure I have my WHERE statement correct before doing something
destructive like a DELETE, all to easy to mis-type or copy and lose data):
$ sqlite3 /home/weewx/archive/weewx.sdb
sqlite> SELECT COUNT(dateTime) FROM archive WHERE
dateTime>=strftime('%s','2017-09-01
00:00:00', 'utc') AND dateTime<strftime('%s','2017-10-01 00:00:00', 'utc');
8377
sqlite>
This is the same as you did as the first check. If you want to delete
everything in your archive BEFORE 1 October then your WHERE would change to:
sqlite> SELECT COUNT(dateTime) FROM archive WHERE
dateTime<strftime('%s','2017-10-01
00:00:00', 'utc');
Either way, if the result is as expected issue the DELETE query (you can
use up arrow to bring up the last sqlite comm and and use the arrow keys to
go back and modify the front end):
sqlite> DELETE FROM archive WHERE dateTime>=strftime('%s','2017-09-01
00:00:00', 'utc') AND dateTime<strftime('%s','2017-10-01 00:00:00', 'utc');
sqlite>
of course deleting all before October 2017 would use:
sqlite> DELETE FROM archive WHERE dateTime<strftime('%s','2017-10-01
00:00:00', 'utc');
Then run the previous SELECT query to check they are gone:
sqlite> SELECT COUNT(dateTime) FROM archive WHERE
dateTime>=strftime('%s','2017-09-01
00:00:00', 'utc') AND dateTime<strftime('%s','2017-10-01 00:00:00', 'utc');
0
sqlite> .quit
$
Run the Sep17 import again or import all months again and see how that
goes, hopefully the log will indicate all records were imported and saved
to archive.
A couple of final points. As you've no doubt worked out the above queries
can be easily modified to look at other months, just change the month
number in the strftime format string. Secondly, a weeWX 'month' starts at
the 1st record AFTER midnight on the 1st and finishes with (includes) the
midnight record on the 1st of the next month; however, according to the
Cumulus monthly logs a month includes the midnight record on the 1st and
stops at the last record before midnight on the following 1st. The
distinction does not matter here but it's worthwhile knowing about this in
the future as you may come across it in the weeWX docs.
Gary
--
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.
For more options, visit https://groups.google.com/d/optout.
