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
On Monday, 24 March 2025 at 4:56:39 pm UTC+11 Dr Henk Harms wrote:
>
> 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)
>
> On Mon, 24 Mar 2025 at 13:38, Dr Henk Harms <drhen...@gmail.com> wrote:
>
>> 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. <ozcz...@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+...@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/13b445d9-2887-45d6-abc1-6879aa7704aen%40googlegroups.com.
