Node-RED + InfluxDB query for computing min/max/avg temperature since start of day

[shutterfreak (Olivier Biot)]

I wanted to share how you can create InfluxQL queries on data persisted in InfluxDB, by means of node-RED.

The example I’m giving here, is the automated update of the daily minimum, average and maximum temperature since midnight. It takes care of daylight saving time by converting local time to ISO time.

The obvious way of doing this in openHAB is to write a rule that is updated whenever a new temperature reading has been posted.

Here I want to expose you how to achieve the same result by using node-red-contrib-influxdb alongside node-red-contrib-openhab2.

Important remark: if you’re using openHAB2.5, you must use node-red-contrib-openhab2@1.1.7 as this is the latest version that is still compatible with openHAB2. Version 1.1.8 is only compatible with openHAB3 and should probably have to be semantically versioned as either 1.2.0 or 3.0.0.

Here’s the general flow:

When using InfluxDB as persistence layer, you can query the time series associated with the item updates persisted through openHAB2 persistence. The way item state is persisted in InfluxDB is:

• Measurement == item name defined in openHAB2
• Measurement value == “value”
• Measurement type depends on item definition in openHAB2

The first function node in the flow is labeled Create InfluxQL min/avg/max query, and creates the query based on the name of the item that received an update. It contains the following code to create the InfluxQL query:

node.debug(`meanSinceStartOfDay(${msg.item})`);
var timestamp = new Date();
var startOfDay = new Date(new Date(timestamp).setHours(0,0,0,0)).toISOString()
var endTime = new Date(new Date(timestamp)).toISOString();
// FluxQL query (influx 1.x)
msg.query = `SELECT MEAN(value) as avg, MIN(value) as min, MAX(value) as max from ${msg.item} WHERE time >= '${startOfDay}'`;

node.debug(`meanSinceStartOfDay(${msg.item}) - msg: ${JSON.stringify(msg)}`);
return msg;

Next, we inject this query to the “InfluxDB in” node. You will have to edit the influxdb configuration node depending on your setup. Since I’m using influxdb version 1.8, I select “Influx v1.*” as version and provide the database name (in y case openhab_db), the InfluxDB user name and password for the InfluxDB user having the proper privileges (i.e. “read”) on the openHAB2 persistence database (in my case, openhanded_db).

Then we move to the second function node titled “Update min/avg/max temperature since start of day” which takes care of parsing the query result and generating the update messages to the openHAB2 items related to the minimum, maximum and average values since the start of the day. Here’s the code:

/*
Source openHAB item:
  Wx_OWM_Current_Temperature
Resulting openHAB items that will contain the daily minimum, average and maximum temperature
since midnight local time:
  Wx_OWM_Current_Temperature_Min
  Wx_OWM_Current_Temperature_Avg
  Wx_OWM_Current_Temperature_Max

Notes:
1. msg.item contains the changed temperature item;
   we will automatically append "_Min", "_Avg" or "_Max" to the item name
   depending on the computed value returned from the query.
2. influxDB query results are contained in a JSON array. In our case, the query
   will only return one record: msg.payload[0].
*/

var outMsgs = [ ];

if (msg.payload && msg.payload.length === 1) {
    // Less typing later on:
    const data = msg.payload[0];
    
    if (!isNaN(data.min)) {
        outMsgs.push({
            item: `${msg.item}_Min`,
            topic: 'ItemUpdate',
            payload: data.min,
        });
    }
    if (!isNaN(data.avg)) {
        outMsgs.push({
            item: `${msg.item}_Avg`,
            topic: 'ItemUpdate',
            payload: data.avg,
        });
    }
    if (!isNaN(data.max)) {
        outMsgs.push({
            item: `${msg.item}_Max`,
            topic: 'ItemUpdate',
            payload: data.max,
        });
    }
}

node.debug(`Update min/avg/max temperature since start of day(${msg.item}) outMsgs: ${JSON.stringify(outMsgs)}`);
if (outMsgs.length > 0) {
    node.debug(`Will send ${outMsgs.length} message(s)`);
    return [ outMsgs ];
}
node.warn("The InfluxQL query returned no results.")
return null;

Finally, we use an openhab2-in node named “min/avg/max temperature since start of day” which has ItemName, Topic and Payload left empty as these values are generated by the 2nd function node.

As a debug view, I also added 3 openhab-out nodes representing the minimum, average and maximum temperatures since midnight.

I hope that this simple example can inspire you in interacting with the influx persistence in node-RED. It also sheds some light on how persistence is implemented.

The example is somehow fragile in that I don’t update the daily average temperature in case I receive an identical temperature reading from OpenWeatherMaps. This can easily be addressed by keeping track of the time the temperature reading was last obtained (and this value always gets an update when new readings have been obtained). This can easily be solved by having a function node generate a message containing the temperature item as msg.item whenever a new reading has been received (I record this time in Wx_OWM_Current_Time). This function node can then trigger the “Create InfluxQL min/avg/max query” node (see above).

Enjoy!