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.
On Tue, 25 Mar 2025 at 22:52, Mike B. <ozcze...@gmail.com> wrote:
> 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
> <https://groups.google.com/d/msgid/weewx-user/13b445d9-2887-45d6-abc1-6879aa7704aen%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/CANcmcPU3J2D5EpPAeomxMGQHYV7%3DixoYk%2BiV5TKsheOA62XTxQ%40mail.gmail.com.
