Node-red-contrib-influxdb function to pass a specific time query

General
1

Hello,

I'm new to Node-RED and javascript... hope you can help!
I want to perform a influxdb query between two specitic timestamps: boot, and now (being now the moment at which GPIO 21 turns to 0).

I don't manage to create a working function node that sends the msg.query to influxdb in node correctly.

I share below my flow in case it helps.

Thanks!

[
    {
        "id": "17f65318.ee1d2d",
        "type": "tab",
        "label": "Flow 3",
        "disabled": false,
        "info": ""
    },
    {
        "id": "dd2abd31.35787",
        "type": "rpi-gpio in",
        "z": "17f65318.ee1d2d",
        "name": "Key OFF",
        "pin": "40",
        "intype": "tri",
        "debounce": "25",
        "read": false,
        "x": 80,
        "y": 40,
        "wires": [
            [
                "e8ded98b.83f858"
            ]
        ]
    },
    {
        "id": "f04219ab.d9b908",
        "type": "influxdb in",
        "z": "17f65318.ee1d2d",
        "influxdb": "9aa3d4b6.12c9b8",
        "name": "max GS vuelo",
        "query": "SELECT max(\"value\") /16203 FROM \"Accel[2]\" WHERE time >= now() - 200d and time < now()",
        "rawOutput": false,
        "precision": "",
        "retentionPolicy": "",
        "org": "organisation",
        "x": 680,
        "y": 40,
        "wires": [
            [
                "2a35fba0.aa0014"
            ]
        ]
    },
    {
        "id": "e8ded98b.83f858",
        "type": "switch",
        "z": "17f65318.ee1d2d",
        "name": "Key off?",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "eq",
                "v": "0",
                "vt": "str"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 1,
        "x": 280,
        "y": 40,
        "wires": [
            [
                "1e8f57c3.271bd8"
            ]
        ]
    },
    {
        "id": "30479452.86686c",
        "type": "inject",
        "z": "17f65318.ee1d2d",
        "name": "key on",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 90,
        "y": 100,
        "wires": [
            [
                "d598061a.2cb6f8"
            ]
        ]
    },
    {
        "id": "d598061a.2cb6f8",
        "type": "change",
        "z": "17f65318.ee1d2d",
        "name": "Set flow.time",
        "rules": [
            {
                "t": "set",
                "p": "time",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 290,
        "y": 100,
        "wires": [
            []
        ]
    },
    {
        "id": "2a35fba0.aa0014",
        "type": "debug",
        "z": "17f65318.ee1d2d",
        "name": "Output 1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 880,
        "y": 40,
        "wires": []
    },
    {
        "id": "1e8f57c3.271bd8",
        "type": "function",
        "z": "17f65318.ee1d2d",
        "name": "",
        "func": "",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 460,
        "y": 40,
        "wires": [
            [
                "f04219ab.d9b908"
            ]
        ]
    },
    {
        "id": "9aa3d4b6.12c9b8",
        "type": "influxdb",
        "hostname": "127.0.0.1",
        "port": "8086",
        "protocol": "http",
        "database": "database",
        "name": "test 1.8 flux",
        "usetls": false,
        "tls": "d50d0c9f.31e858",
        "influxdbVersion": "1.8-flux",
        "url": "https://localhost:8086",
        "rejectUnauthorized": false
    },
    {
        "id": "d50d0c9f.31e858",
        "type": "tls-config",
        "name": "",
        "cert": "",
        "key": "",
        "ca": "",
        "certname": "",
        "keyname": "",
        "caname": "",
        "servername": "",
        "verifyservercert": false
    }
]
2

Welcome to the forum.

You have configured the influx server node as 1.8 (flux), which means that you are running influxdb v1.8 but want to use the flux query language (which 1.8 can be configured to support). The query that you have used is not a flux query. I think you probably want to configure it as 1.x, not 1.8 flux.

3

Hi Colin,

Thanks for your support! I do have configured the node as 1.x. However I changed the server before posting to avoid sharing my IP.
The node "influxdb in" works fine. Just want to send the stationary query from a previous function.
My problem is that I do not know how to prepare this query function. I tried with context variables, and following this post:

Although I set a msg.query, my function always return the original message from its inject node (timestamp).

Thanks again for your time,

Cheers,

Adrián

4

I don't understand what you mean by that.

Send the message you are passing to the node into a debug node set to Output Complete Message and show us what it says. If necessary, click on the object in the debug pane to expand it so it is clear.
Before you paste it here though, check that msg.query looks right. Clear the query in the node so that it knows you are passing one in.

[Edit] Are you sure you have setup msg.query and not msg.payload.query?

5

Hi Colin,

I am having issues (maybe with quotes?) in my query.
Not sure though if the issue is the name of the influxdb variable ("Accel**[**2]").
Is "[" a prohibited character?
To finish with, I am not sure if I am formating correcty the timestamps.

Function code:

var graf = flow.get("time");
//working query in Influxdb in node: SELECT max("value") /16203 FROM "Accel[2]" WHERE time >= now() - 200d and time < now()
now= new Date(msg.topic).getTime();
before= new Date(graf).getTime();
    let tstart = now; 
    let tend = before;
//node.warn("tstart = " + tstart);
//node.warn("tend = " + tend);
    let sel    = "SELECT max(value) /16203 FROM Accel[2]";
    let q      = sel + "WHERE time >= '" + tstart + "' AND time <= '" + tend + "'";
    msg.query = q;

return msg;

Debug message:

29/11/2021 20:01:45node: Output 1
1638212505848 : msg : Object
object
_msgid: "fe891381.dcd13"
payload: false
topic: 2021-11-29T19:01:45.848Z
query: "SELECT max(value) /16203 FROM Accel[2]WHERE time >= '1638212505848' AND time <= '1638212504120'"

and error message from "influxdb in" node output:

Error: A 400 Bad Request error occurred: {"error":"error parsing query: found [, expected ; at line 1, char 36"}

Thanks again for your support,

Regards,

Adrián

6

Is Accel[[2] the Measurement name?

The best thing is to use the influx command line program to test the query manually. When it works there then you will know what you need in node red.

You might need the timestamps in nanoseconds.

7

Having looked at the influx 1.8 query language docs (Influx Query Language (InfluxQL) | InfluxDB OSS 1.8 Documentation), if the Measurement (or any identifier) contains special characters then it must be double quoted. So if the measurement name is Accel[2] (which would be a bad idea in my opinion) then in the query you must put it in double quotes, so "Accel[2]". Also timestamps are numbers so they should not have quotes round them.

8

Hi Colin,

Yes, "Accel[2]" is the name of the measurement.
I am trying with another measurement query.
Now I have it working if I set a fix timestamp for "start" and "end", like in the other post:

var before = flow.get("time");

now= msg.topic;

    let tstart = "2021-09-30T13:29:52.738Z"; 
    let tend = "2021-11-29T16:29:52.738Z";

    msg.query = `SELECT max(IAS) FROM AOAsensor WHERE time >= '${tstart}' AND time <= '${tend}'`
    msg.before= before;
    msg.now=now;

return msg;

This works. However I am having issues when I try to pass tstart an tend from timestamp payload and flow context variable, although they seemed to be properly formated (see msg.before and msg.now from the debug node):

var before = flow.get("time");
now= msg.topic;

    let tstart = now; 
    let tend = before;
    msg.query = `SELECT max(IAS) FROM AOAsensor WHERE time >= '${tstart}' AND time <= '${tend}'`
    msg.before= before;
    msg.now=now;

return msg;

Debug node:

object
_msgid: "3babdadf.8c9a96"
payload: false
topic: 1638266302810
query: "SELECT max(IAS) FROM AOAsensor WHERE time >= '1638266302810' AND time <= '1638266301656'"
before: 2021-11-30T09:58:21.656Z
now: 2021-11-30T09:58:22.810Z

Can you give me a clue of how should I manage timestamps?

Thank you!

9

To quote from my previous message

Also

You can tell that if you look at the timestamps to see whether they are in nanosecs (rather than the milliseconds that you are supplying) by looking in the influx command line app. If you have not got that going yet then do that. It will save you time in the long run. It will be much quicker to try something there rather than having to ask here.

10

Hi Colin,

You can close this thread. Thanks for your support.

It is working now, this is the working code just in case someone has issues as well with timestamp formats for Influxdb In queries:

var before = flow.get("time");
var startOfDay = new Date(before).toISOString();
msg.query = `SELECT max(IAS) FROM AOAsensor WHERE time >= '${startOfDay}'`;
return msg;
11

Mark one of the posts as the solution (your own in this case possibly) then it will be marked solved.

12

Now of course you are supplying a string rather than a number so you do need the quotes round it. It would be much more efficient to do something like

var before = flow.get("time");
msg.query = `SELECT max(IAS) FROM AOAsensor WHERE time >= ${before*1000000}`;
return msg;

That is assuming that your timestamps are in nanoseconds. Did you determine that from the influx command line tool?

13

Your solution works too. Thanks for your guideance.
The complete msg object debug node helped me to understand the messages... thanks for that too.

It was quite confusing the quoting.

14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.