I'm sure I'm not the only one who was looking to do this, so the wiki makes
sense. If this goes into the wiki, a more automated approach would probably
be best.
Here's what I ended up doing. A little more manual than I'd like but I had
my MySQL database in SQLite already so I just worked on that database as my
test database.
1. I ran the SQL Query below and did a manual QC spot check on the
records.
2. When I felt like it seemed acceptable, I just exported the temporary
table from SQLite to CSV (using DB Browser for SQLite on Windows)
3. Then on MySQL I did DELETE FROM archive WHERE `interval` = 1;
4. Then imported the CSV file to MySQL
If someone could review the aggregation for each observation I chose, that
would be appreciated. (I have backups so I can always do this again if it's
not right.)
create temporary table cons5min as
select max(dateTime) as dateTime,
usUnits,
5 as interval,
avg(barometer) as barometer,
avg(pressure) as pressure,
avg(altimeter) as altimeter,
avg(inTemp) as inTemp,
avg(outTemp) as outTemp,
avg(inHumidity) as inHumidity,
avg(outHumidity) as outHumidity,
avg(windSpeed) as windSpeed,
max(windDir) as windDir,
max(windGust) as windGust,
avg(windGustDir) as windGustDir,
avg(rainRate) as rainRate,
avg(rain) as rain,
avg(dewpoint) as dewpoint,
avg(windchill) as windchill,
avg(heatindex) as heatindex,
avg(ET) as ET,
avg(radiation) as radiation,
avg(UV) as UV,
avg(extraTemp1) as extraTemp1,
avg(extraTemp2) as extraTemp2,
avg(extraTemp3) as extraTemp3,
avg(soilTemp1) as soilTemp1,
avg(soilTemp2) as soilTemp2,
avg(soilTemp3) as soilTemp3,
avg(soilTemp4) as soilTemp4,
avg(leafTemp1) as leafTemp1,
avg(leafTemp2) as leafTemp2,
avg(extraHumid1) as extraHumid1,
avg(extraHumid2) as extraHumid2,
avg(soilMoist1) as soilMoist1,
avg(soilMoist2) as soilMoist2,
avg(soilMoist3) as soilMoist3,
avg(soilMoist4) as soilMoist4,
avg(leafWet1) as leafWet1,
avg(leafWet2) as leafWet2,
avg(rxCheckPercent) as rxCheckPercent,
avg(txBatteryStatus) as txBatteryStatus,
avg(consBatteryVoltage) as consBatteryVoltage,
avg(hail) as hail,
avg(hailRate) as hailRate,
avg(heatingTemp) as heatingTemp,
avg(heatingVoltage) as heatingVoltage,
avg(supplyVoltage) as supplyVoltage,
avg(referenceVoltage) as referenceVoltage,
avg(windBatteryStatus) as windBatteryStatus,
avg(rainBatteryStatus) as rainBatteryStatus,
avg(outTempBatteryStatus) as outTempBatteryStatus,
avg(inTempBatteryStatus) as inTempBatteryStatus
FROM archive WHERE interval = 1
GROUP BY (dateTime-60) / 300;
On Friday, October 18, 2019 at 2:07:44 AM UTC-4, Cameron D wrote:
>
> If is worth a wiki entry, then much better if Pat could post the full
> script - my version has lots of bits left to the imagination.
>
> On Friday, 18 October 2019 00:32:02 UTC+10, Vince Skahan wrote:
>>
>> On Wednesday, October 16, 2019 at 6:09:26 PM UTC-7, Cameron D wrote:
>>>
>>> Is there any reason you could not just use sqlite's own consolidation
>>> functions...
>>>
>>>
>> oooh that's pretty slick - perhaps wiki-worthy ?????
>>
>
--
You received this message because you are subscribed to the Google Groups
"weewx-development" 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-development/12d539d3-ef47-425b-ae92-c9850b85031c%40googlegroups.com.
