Weewx 5.1 - Observation over 2 days

[Mike B.]

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

[Dr Henk Harms]

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? 

--
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+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/31513f26-63d1-4e47-a903-9c584ba64a7cn%40googlegroups.com.

[Dr Henk Harms]

So been "messing with it" 
To create a table. 

CREATE TABLE archive_bom_minmax (
    dateTime INTEGER NOT NULL PRIMARY KEY,
    usUnits INTEGER NOT NULL,
    `interval` INTEGER NOT NULL,
    min_temp DOUBLE,
    min_temp_time INTEGER,
    max_temp DOUBLE,
    max_temp_time INTEGER
);

Then add this to /usr/share/weewx/user

#!/usr/bin/env python

# -*- coding: utf-8 -*-

"""

bom_minmax.py - WeeWX service to record BoM-compliant min/max temperatures

 

This service records minimum and maximum temperatures between 6pm the previous

day and 9am the current day, storing them at 9am in a dedicated table.

"""

import datetime

import time

import weedb

import weewx

from weewx.engine import StdService

# Try to use new-style WeeWX V5 logging

try:

    import logging

    log = logging.getLogger(__name__)

    def logdbg(msg):

        log.debug(msg)

    def loginf(msg):

        log.info(msg)

    def logerr(msg):

        log.error(msg)

except (ImportError, AttributeError):

    # Use old-style WeeWX V4 logging via syslog

    import syslog

    def logmsg(level, msg):

        syslog.syslog(level, 'bom_minmax: %s' % msg)

    def logdbg(msg):

        logmsg(syslog.LOG_DEBUG, msg)

    def loginf(msg):

        logmsg(syslog.LOG_INFO, msg)

    def logerr(msg):

        logmsg(syslog.LOG_ERR, msg)

# BoM observation time configuration

BOM_MORNING_HOUR = 9    # 9am

BOM_EVENING_HOUR = 18   # 6pm

class BomMinMaxService(StdService):

    """Service to record min/max temperatures in BoM format."""

   

    def __init__(self, engine, config_dict):

        super(BomMinMaxService, self).__init__(engine, config_dict)

       

        loginf("Starting BoM Min/Max Temperature Service")

       

        # Get the database configuration

        self.db_dict = config_dict.get('BomMinMax', {})

       

        # Archive database binding - where to get temperature data from

        self.archive_binding = self.db_dict.get('archive_binding', 'wx_binding')

       

        # Output database binding - where to store BoM min/max data

        self.output_binding = self.db_dict.get('output_binding', 'wx_binding')

       

        # Table name for BoM min/max data

        self.table_name = self.db_dict.get('table_name', 'archive_bom_minmax')

       

        # Temperature field to use

        self.temp_field = self.db_dict.get('temp_field', 'outTemp')

       

        # Initialize the table if it doesn't exist

        # Commented out for stability

        #self.init_table()

       

        # Bind to archive events

        self.bind(weewx.NEW_ARCHIVE_RECORD, self.new_archive_record)

       

        loginf("BoM Min/Max Temperature Service initialized")

   

    def init_table(self):

        """Initialize the table if it doesn't exist."""

       

        try:

            with self.engine.db_binder.get_manager(self.output_binding) as dbm:

                # Check if table exists

                if self.table_name not in dbm.connection.tables():

                    loginf(f"Creating table {self.table_name}")

                   

                    # Create the table

                    dbm.connection.execute(

                        f"CREATE TABLE {self.table_name} ("

                        f"dateTime INTEGER NOT NULL PRIMARY KEY, "

                        f"usUnits INTEGER NOT NULL, "

                        f"`interval` INTEGER NOT NULL, "

                        f"min_temp DOUBLE, "

                        f"min_temp_time INTEGER, "

                        f"max_temp DOUBLE, "

                        f"max_temp_time INTEGER)"

                    )

        except Exception as e:

            logerr(f"Error initializing table: {e}")

   

    def new_archive_record(self, event):

        """Called when a new archive record is available.

       

        Check if it's just after 9am and if so, record the min/max data

        for the 6pm-9am period.

        """

       

        timestamp = event.record['dateTime']

        dt = datetime.datetime.fromtimestamp(timestamp)

       

        # Only process records at or just after 9am

        if dt.hour == BOM_MORNING_HOUR and dt.minute < 10:

            self.process_bom_minmax(timestamp)

   

    def process_bom_minmax(self, current_ts):

        """Process and record BoM min/max temperatures.

       

        Args:

            current_ts: Current timestamp (around 9am)

        """

       

        # Convert timestamp to datetime

        current_dt = datetime.datetime.fromtimestamp(current_ts)

       

        # Calculate the timestamp for 6pm yesterday

        yesterday = current_dt - datetime.timedelta(days=1)

        yesterday_6pm = datetime.datetime(yesterday.year, yesterday.month,

                                         yesterday.day, BOM_EVENING_HOUR, 0, 0)

        start_ts = int(yesterday_6pm.timestamp())

       

        # Calculate the timestamp for 9am today

        today_9am = datetime.datetime(current_dt.year, current_dt.month,

                                     current_dt.day, BOM_MORNING_HOUR, 0, 0)

        end_ts = int(today_9am.timestamp())

       

        loginf(f"Processing BoM min/max data for {yesterday_6pm.strftime('%Y-%m-%d %H:%M')} to {today_9am.strftime('%Y-%m-%d %H:%M')}")

       

        try:

            # Get the temperature data from the archive

            with self.engine.db_binder.get_manager(self.archive_binding) as dbm_archive:

                # Get min temperature

                min_temp_data = dbm_archive.getSql(

                    f"SELECT MIN({self.temp_field}), dateTime, usUnits FROM archive "

                    f"WHERE dateTime >= {start_ts} AND dateTime <= {end_ts} "

                    f"AND {self.temp_field} IS NOT NULL")

               

                # Get max temperature

                max_temp_data = dbm_archive.getSql(

                    f"SELECT MAX({self.temp_field}), dateTime, usUnits FROM archive "

                    f"WHERE dateTime >= {start_ts} AND dateTime <= {end_ts} "

                    f"AND {self.temp_field} IS NOT NULL")

               

                # Skip if no data available

                if not min_temp_data or not max_temp_data:

                    loginf("Insufficient temperature data for period, skipping")

                    return

               

                # Create the record

                record = {

                    'dateTime': end_ts,

                    'usUnits': min_temp_data[2],  # Use the same unit system from archive

                    'interval': 1440,  # 24 hours in minutes

                    'min_temp': min_temp_data[0],

                    'min_temp_time': min_temp_data[1],

                    'max_temp': max_temp_data[0],

                    'max_temp_time': max_temp_data[1]

                }

               

                # Store in the output database

                with self.engine.db_binder.get_manager(self.output_binding) as dbm_output:

                    dbm_output.addRecord(record)

               

                # Log the min/max data

                min_temp_time = datetime.datetime.fromtimestamp(min_temp_data[1])

                max_temp_time = datetime.datetime.fromtimestamp(max_temp_data[1])

               

                loginf(f"Recorded BoM min/max: Min {min_temp_data[0]:.1f}°C at {min_temp_time.strftime('%H:%M')}, "

                       f"Max {max_temp_data[0]:.1f}°C at {max_temp_time.strftime('%H:%M')}")

       

        except Exception as e:

            logerr(f"Error processing BoM min/max: {e}")

# To use this service, add to weewx.conf:

#

# [BomMinMax]

#     # Database binding to read temperature data from

#     archive_binding = wx_binding

#     # Database binding to store BoM min/max data (can be same as archive_binding)

#     output_binding = wx_binding

#     # Table name for BoM min/max data

#     table_name = archive_bom_minmax

#     # Temperature field to monitor (default: outTemp)

#     temp_field = outTemp

#

# [Engine]

#     [[Services]]

#         data_services = ..., user.bom_minmax.BomMinMaxService, ...

That should work, I disable the table checking as that caused me to error. 
I also installed weewx as a package not via pip. 

Guess we know tomorrow here in AU if it works :-). 

Let me know how you go. 
H. 
(https://dayboro.au)

[michael.k...@gmx.at]

Didn't Karen K have something of this sort in her repo?

The above will only work with metric db, will it?

[Dr Henk Harms]

Just new here... do not know karen k, or about her repo yet... where is her repo is it on github? 
Yes AU is metric, I assume if you set the it in conf it will do what it does... 

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/6508b98a-abff-425d-8c84-c417cafb13ddn%40googlegroups.com.

[Mike B.]

Dr Henk Harms - Firstly thank you for taking timeout and putting together the solution above. I think its pretty close.

When trying to add the record using:

                with self.engine.db_binder.get_manager(self.output_binding) as dbm_output:
                    dbm_output.addRecord(record)

I get the following error:

              ERROR weewx.manager: Unable to add record 2025-03-24 18:00:00 AEDT (1742799600) to database 'weewx': (1062, "Duplicate entry '1742799600' for key 'PRIMARY'")

I added logging to mariadb so I could see what its trying to do and it looks like its trying to insert the new record into "archive" instead of the custom table. How do we tell weewx to use our custom table?

Thanks heaps

Mike

(highlight is added by me)

                              Query    SELECT MAX(dateTime) FROM archive
                 90543 Query    SELECT SUM(rain), MIN(usUnits), MAX(usUnits) FROM archive WHERE dateTime>1742902862.0e0 AND dateTime<=1742906462
                 90541 Query    SELECT MIN(outTemp), dateTime, usUnits FROM archive WHERE dateTime >= 1742799600 AND dateTime <= 1742853600 AND outTemp IS NOT NULL
                 90543 Query    SELECT SUM(rain), MIN(usUnits), MAX(usUnits) FROM archive WHERE dateTime>1742820062.0e0 AND dateTime<=1742906462
                 90541 Query    SELECT MAX(outTemp), dateTime, usUnits FROM archive WHERE dateTime >= 1742799600 AND dateTime <= 1742853600 AND outTemp IS NOT NULL
                 90543 Query    SELECT SUM(rain), MIN(usUnits), MAX(usUnits) FROM archive WHERE dateTime>=1742821200 AND dateTime<=1742906462
                 90541 Query    START TRANSACTION
                 90541 Query    INSERT INTO archive (`interval`,`dateTime`,`usUnits`) VALUES (1440,1742799600,1)
                 90541 Query    COMMIT
                 90541 Quit
                 90543 Quit
                 90542 Quit

[Dr Henk Harms]

Ill be on the road for the next 2.5 weeks not having much access to internet, when I am back in the office I will fix that up for you. 

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/13b445d9-2887-45d6-abc1-6879aa7704aen%40googlegroups.com.