Almost. The aggregation type is wrong for some of the columns. You want sum(rain) as rain sum(ET) as ET sum(hail) as hail
There is also the problem of windDir and windGustDir. To do these properly, you have to switch from polar notation (scalar, direction) to vector components (xvec, yvec), and do the averaging with the latter. Something like (I'm making this up and it is NOT TESTED): avg(windSpeed * cos((90.0 - windDir) / 57.3)) as xvec, avg(windSpeed * sin((90.0 - windDir) / 57.3)) as yvec, avg(windSpeed) as windSpeed, 90.0 - arctan2(yvec, xvec) * 57.3 as windDir No idea whether SQL offers math functions, but hopefully you get the idea. -tk On Fri, Oct 18, 2019 at 5:46 AM Pat <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/weewx-development/12d539d3-ef47-425b-ae92-c9850b85031c%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAPq0zED9OAs7KYsUVoh_oLYbsPdzgB00MpPWgANFs7wrnm8%2ByQ%40mail.gmail.com.
