I ran that query or similar on my mariadb... took forever. Would it not be simpler to have an table called archive_day_bom_minmax and have a weewx service write to that table at 9am each day?
On Thu, 20 Mar 2025 at 18:31, Mike B. <ozcze...@gmail.com> wrote: > Hi All, > > I am trying to capture a new observation: "Highest Nighttime Min > Temperature". I am using this logic "The running min, a value the Bureau Of > Meteorology uses to highlight the lowest temperature between 6pm and 9am" > > I have a query against archive table (MariaDb) > > SELECT (x.night + INTERVAL 1 DAY) AS night, > UNIX_TIMESTAMP(x.night), > x.lowestNightTemp > FROM ( > SELECT CAST(FROM_UNIXTIME(dateTime) AS DATE) - INTERVAL 9 > HOUR AS night, > MAX((a.outTemp-32)*(5/9)) AS highestNightTemp, > MIN((a.outTemp-32)*(5/9)) AS lowestNightTemp, > DATE_ADD(DATE_SUB(CAST(FROM_UNIXTIME(dateTime) AS > DATE), INTERVAL 1 DAY), INTERVAL 18 HOUR) AS DayBefore, > DATE_ADD(CAST(FROM_UNIXTIME(dateTime) AS DATE), > INTERVAL 9 HOUR) AS Day > FROM archive AS a > WHERE a.dateTime BETWEEN > UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CAST > (FROM_UNIXTIME(dateTime) AS DATE), INTERVAL 1 DAY), INTERVAL 18 HOUR)) > AND > UNIX_TIMESTAMP(DATE_ADD(CAST(FROM_UNIXTIME( > dateTime) AS DATE), INTERVAL 9 HOUR)) > GROUP BY night > ) AS x > ORDER BY x.lowestNightTemp DESC LIMIT 1 > > The above query seems to work, as it returns the Highest lowest > Temperature between 6pm and 9am. > > I am trying to work out how to integrate this into the weewx process. I > want to: > * Run a service or something once a day (say midnight) and then for each > "day" (defined as 6pm to 9am) store the summary > * If I run rebuild daily summaries (weectl database) ideally this process > should also run > * Add result to records to have something like: > "Highest Minimum overnight Temperature 29.111 C 23 Mar 2025" > > I created a service "class NighttimeTempTracker(StdService)" and added it > to "data_services"...but it runs for each ARCHIVE PERIOD > (weewx.NEW_ARCHIVE_RECORD) > and cannot span multiple days. > > I am out of ideas in which ways I can achieve the above via weewx process. > Any help, pointers, sample code or projects would be highly appreciated. > > Thanks > > Mike > > > -- > 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. > To view this discussion visit > https://groups.google.com/d/msgid/weewx-user/31513f26-63d1-4e47-a903-9c584ba64a7cn%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/31513f26-63d1-4e47-a903-9c584ba64a7cn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CANcmcPWze5%3D7mQuHjmNqqMPmkJfxqrjwUDW%2BkmJd-z41nsY0Cw%40mail.gmail.com.
