I had already tried it in sqlite3 - the calcs were done as INT without needing to cast. Not sure about mysql. OTOH, a FLOOR will make it doubly safe.
On Thursday, 17 October 2019 11:35:39 UTC+10, Tom Keffer wrote: > > One thing though: you might have to FLOOR the "GROUP BY" clause: > > create temporary table cons5min as > select max(dateTime) as dateTime, > usUnits, 5 as interval, > avg(barometer) as barometer, > avg(inTemp) as inTemp, > <all the other data> > from archive > where interval = 1 > group by FLOOR((dateTime-60) / 300); > > Otherwise thing get grouped by the floating point result. That is, they > don't get grouped at all. > > > > On Wed, Oct 16, 2019 at 6:31 PM Pat <[email protected] <javascript:>> > wrote: > >> Excellent. Good to know. I have a disposable SQLite already setup to go. >> Working on finishing the query now as practice. We already know my SQL is a >> little weak tonight :-) >> >> On Wednesday, October 16, 2019 at 9:29:35 PM UTC-4, Tom Keffer wrote: >>> >>> The command should work in MySQL as well. >>> >>> On Wed, Oct 16, 2019 at 6:19 PM Pat <[email protected]> wrote: >>> >>>> This is great! >>>> >>>> I use MySQL primarily as my source, but frequently flip/flop for >>>> testing various things. I can export the 20,000 rows to SQLite, run this >>>> then import the new rows back to MySQL. Thanks! >>>> >>>> On Wednesday, October 16, 2019 at 9:09:26 PM UTC-4, Cameron D wrote: >>>>> >>>>> Is there any reason you could not just use sqlite's own consolidation >>>>> functions... >>>>> >>>>> create temporary table cons5min as >>>>> select max(dateTime) as dateTime, >>>>> usUnits, 5 as interval, >>>>> avg(barometer) as barometer, >>>>> avg(inTemp) as inTemp, >>>>> <all the other data> >>>>> from archive >>>>> where interval = 1 >>>>> group by (dateTime-60) / 300 ; >>>>> >>>>> >>>>> >>>>> choosing appropriate functions, such as max for windgust >>>>> >>>>> then delete the lines with interval=1 >>>>> and >>>>> insert into archive select * from cons5min; >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Wednesday, 16 October 2019 08:10:21 UTC+10, Pat wrote: >>>>>> >>>>>> Back when I started with weewx in 2015 I had a 1 minute archive >>>>>> interval. I kept it that way for a few months until I realized more in >>>>>> how >>>>>> the archive interval works. >>>>>> >>>>>> I am performing some wee_import functions to get calc_missing on some >>>>>> records. wee_import warns about the mismatch in the intervals, which got >>>>>> me >>>>>> thinking: >>>>>> >>>>>> Is it possible to consolidate all my "1" interval to match the rest >>>>>> of my database at 5 minute intervals? >>>>>> >>>>> -- >>>> 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/003757b5-bf5b-45d1-b218-a21e81db650f%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/weewx-development/003757b5-bf5b-45d1-b218-a21e81db650f%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] <javascript:>. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/weewx-development/65ecf732-75a7-4852-88bb-225b10cb2949%40googlegroups.com >> >> <https://groups.google.com/d/msgid/weewx-development/65ecf732-75a7-4852-88bb-225b10cb2949%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/0c183a26-ddfe-42af-9110-ca901a52ba5c%40googlegroups.com.
