Last record that is not NULL

[steep...@gmail.com]

Hi,

Trying to find a 'WeeWX way' to find the last non-NULL record from the database using a .tmpl file.

Nothing obvious jumps out for me from the documentation. Does anybody have any ideas?

Thanks,

Ian

[Tom Keffer]

The aggregation type 'last' should do what you want:

<p>The last non-null temperature this month is: $month.outTemp.last</p>

--
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 on the web visit https://groups.google.com/d/msgid/weewx-user/6f4aed6f-824c-4ea3-8114-0c979e2e263cn%40googlegroups.com.

[steeple ian]

Thanks Tom,

It worked fine, I did actually try that and it returned a zero (not realising I had a zero in the database) and thought it had not worked correctly. So if I want to eliminate the zeros as well?

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEDUB8krNBpD88f%3Do2ARnr5O_0%3DKd5mhvWRRMG%3DN5dotzw%40mail.gmail.com.

[Tom Keffer]

Then you would have to write an xtypes extension. 

It would look something like this (NOT TESTED):

import weedb
import weewx.xtypes

class LastNonZero(weewx.xtypes.XType):
   
    def get_aggregate(self, obs_type, timespan, aggregate_type, db_manager, **option_dict):
        if aggregate_type != 'lastnonzero':
            raise weewx.UnknownAggregation(aggregate_type)
       
        interpolate_dict = {
            'aggregate_type': aggregate_type,
            'obs_type': obs_type,
            'table_name': db_manager.table_name,
            'start': timespan.start,
            'stop': timespan.stop
        }

        select_stmt = "SELECT %(obs_type)s FROM %(table_name)s " \
                      "WHERE dateTime > %(start)s AND dateTime <= %(stop)s " \
                      "AND %(obs_type)s IS NOT NULL " \
                      "AND %(obs_type)s != 0 " \
                      "ORDER BY dateTime DESC LIMIT 1" % interpolate_dict

        try:
            row = db_manager.getSql(select_stmt)
        except weedb.NoColumnError:
            raise weewx.UnknownType(obs_type)

        value = row[0] if row else None

        u, g = weewx.units.getStandardUnitType(db_manager.std_unit_system, obs_type,
                                               aggregate_type)
        return weewx.units.ValueTuple(value, u, g)

Then you could use it:

<p>This month's last non-null and non-zero temperature is $month.outTemp.lastnonzero</p>

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CADASSaQHNqwQ8RumEkeBOpSh7bEumo%3DG-hC3XuyiiHMa9tkdvg%40mail.gmail.com.

[steeple ian]

Thanks Tom,

I set your code up as a service following the vaporpressure.py example and get the following errors on restart: -

Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****    File "/usr/share/weewx/weewxd", line 148, in main
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****      engine = weewx.engine.StdEngine(config_dict)
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****    File "/usr/share/weewx/weewx/engine.py", line 81, in __init__
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****      self.loadServices(config_dict)
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****    File "/usr/share/weewx/weewx/engine.py", line 153, in loadServices
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****      obj = weeutil.weeutil.get_object(svc)(self, config_dict)
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****    File "/usr/share/weewx/user/lastnonzero.py", line 84, in __init__
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****      self.nz = LastNonZero(algorithm)
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****  TypeError: LastNonZero() takes no arguments
Nov 17 17:39:15 raspberrypi python3[7172]: weewx[7172] CRITICAL __main__:     ****  Exiting.

Code is below

#
#    Copyright (c) 2020 Tom Keffer <tke...@gmail.com>
#
#    See the file LICENSE.txt for your full rights.
#
"""This example shows how to extend the XTypes system with a new type, lastnonzero, the last non-null or non-zero in a record

REQUIRES WeeWX V4.2 OR LATER!

To use:
    1. Stop weewxd
    2. Put this file in your user subdirectory.
    3. In weewx.conf, subsection [Engine][[Services]], add LastNonZero to the list
    "xtype_services". For example, this means changing this

        [Engine]
            [[Services]]
                xtype_services = weewx.wxxtypes.StdWXXTypes, weewx.wxxtypes.StdPressureCooker, weewx.wxxtypes.StdRainRater

    to this:

        [Engine]
            [[Services]]
                xtype_services = weewx.wxxtypes.StdWXXTypes, weewx.wxxtypes.StdPressureCooker, weewx.wxxtypes.StdRainRater, user.lastnonzero.LastNonZeroService

    4. Optionally, add the following section to weewx.conf:
        [LastNonZero]
            algorithm = simple   # Or tetens

    5. Restart weewxd

"""
from weewx.engine import StdService
import weedb
import weewx.xtypes
import datetime

class LastNonZero(weewx.xtypes.XType):
   
    def get_aggregate(self, obs_type, timespan, aggregate_type, db_manager, **option_dict):
        if aggregate_type != 'lastnonzero':
            raise weewx.UnknownAggregation(aggregate_type)
       
        interpolate_dict = {
            'aggregate_type': aggregate_type,
            'obs_type': obs_type,
            'table_name': db_manager.table_name,
            'start': timespan.start,
            'stop': timespan.stop
        }

        select_stmt = "SELECT %(obs_type)s FROM %(table_name)s " \
                      "WHERE dateTime > %(start)s AND dateTime <= %(stop)s " \
                      "AND %(obs_type)s IS NOT NULL " \
                      "AND %(obs_type)s != 0 " \
                      "ORDER BY dateTime DESC LIMIT 1" % interpolate_dict

        try:
            row = db_manager.getSql(select_stmt)
        except weedb.NoColumnError:
            raise weewx.UnknownType(obs_type)

        value = row[0] if row else None

        u, g = weewx.units.getStandardUnitType(db_manager.std_unit_system, obs_type,
                                               aggregate_type)
        return weewx.units.ValueTuple(value, u, g)

class LastNonZeroService(StdService):
    """ WeeWX service whose job is to register the XTypes extension LastNonZero with the
    XType system.
    """

    def __init__(self, engine, config_dict):
        super(LastNonZeroService, self).__init__(engine, config_dict)

        # Get the desired algorithm. Default to "simple".
        try:
            algorithm = config_dict['LastNonZero']['algorithm']
        except KeyError:
            algorithm = 'simple'

        # Instantiate an instance of LastNonZero:
        self.nz = LastNonZero(algorithm)
        # Register it:
        weewx.xtypes.xtypes.append(self.nz)

    def shutDown(self):
        # Remove the registered instance:
        weewx.xtypes.xtypes.remove(self.nz)


# Tell the unit system what group our new observation type, 'lastnonzero, belongs to:
weewx.units.obs_group_dict['lastnonzero'] = "group_distance"

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zECTU7Nn6g3c%2BdiQFQyKpum0gKT7NEaV3ybpeMW11zfL9Q%40mail.gmail.com.

[Tom Keffer]

You're taking the example too literally. It has an option 'algorithm', which your extension does not. 

   def __init__(self, engine, config_dict):
        super(LastNonZeroService, self).__init__(engine, config_dict)

        # Instantiate an instance of LastNonZero:

        self.nz = LastNonZero()
        # Register it:
        weewx.xtypes.xtypes.append(self.nz)

Secondly, lastnonzero is an aggregation type, not an observation type. So, it should not be added to obs_group_dict.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CADASSaRMwg1PvFhCL411QQoXqM23N0neCe-WdBcJauKY4se7%2Bw%40mail.gmail.com.

[steeple ian]

Tom,

That worked perfectly.

Thank you very much for your help.

Ian

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zECU2CkPzNEFTesL41o0KzWjs6g3Nf3KYGmv3uToCUOLmA%40mail.gmail.com.