Node-red-contrib-influxdb, select with absolute time range ERROR

[henkkas]

Pls Help,

I am trying to get data from a table in influx database with a query. Query is passed through influxDB in node. In the query I use WHERE condition with a variable absolute time range which can be manipulated.
I get an error from influxDB:

"Error from InfluxDB: invalid operation: time and *influxql.VarRef are not compatible"

Time format in the var. tstart and tend is: "2021-10-30T14:50:30.833Z"of
(I also tried with number of msec.)
The influxDB in node is set on msec.

In the CLI this selection is working correct.
Maybe it has something to do with single and double quotes?
CLI doesn't work if I change selection where dates are double quotes.
Datetime shows double quotes in debug window.

Anybody can hint me in the right direction?

// Function prepare chart data

if ((topic == "back") || (topic == "forward") || (topic == "periode")) {
    let tstart = context.get("chart.starttime"); 
    let tend   = context.get("chart.endtime"); 
    let sel    = "SELECT time, selfuse, totalsolar FROM power ";
    let q      = sel + "WHERE time >= tstart AND time <= tend";
    msg.query = q;

//node.warn("tstart = " + tstart);
//node.warn("tend = " + tend);
}

[Colin]

Feed the function into a debug node set to Output Full Message and check what is in msg.topic. If you cannot see what is wrong with it then post the debug output here.

[E1cid]

@henkkas

let q = sel + "WHERE time >= tstart AND time <= tend";

should be

let q      = sel + "WHERE time >= " + tstart + "AND time <= " + tend;

[henkkas]

@E1cid , I changed the query as you suggested, it made sense to me, also added a space before AND. But not quite the solution it seems. I got next error in the debug window:

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

[henkkas]

@Colin the output of the function where query is made, gives me the following:

"SELECT time, selfuse, totalsolar FROM power WHERE time >= Invalid Date AND time <= Invalid Date"

gr.
Henk

[E1cid]

Have you tried single quote and/or removing the "T" from the string.

let q      = sel + "WHERE time >= '" + tstart + "' AND time <= '" + tend + "'";

https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#examples-11

https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#rfc3339_like_date_time_string

Also what are the context stored values?

[Colin]

@henkkas

SELECT time, selfuse, totalsolar FROM power WHERE time >= Invalid Date AND time <= Invalid Date"

Well it is not surprising that the query fails is it?

Add node.warn() statements showing tstart and tend just before the line creating the query.

[henkkas]

@E1cid , indeed I read about it, but how do you get rid of the double quotes that already seems present in the time format of javascript?

[E1cid]

You don't, they are removed by the Javascript. See my example of adding single quotes above.

[henkkas]

@Colin and @E1cid , sorry I still had some expirements active, hence the invalid dates,
I now seem to have a valid query, need to solve some more time calculations, though.
Thanks you all for help.

Gr.
Henk

[henkkas]

@Colin and @E1cid. Ok, now it's working fine

// Query maken op de influx database
if ((topic == "back") || (topic == "forward") || (topic == "periode")) {
    let tstart = "2021-10-31T13:29:52.738Z"; 
    let tend = "2021-10-31T16:29:52.738Z";

//    let tstart = context.get("chart.starttime"); 
//    let tend   = context.get("chart.endtime"); 
//node.warn("tstart = " + tstart);
//node.warn("tend = " + tend);
    let sel    = "SELECT time, selfuse, totalsolar FROM power ";
    let q      = sel + "WHERE time >= '" + tstart + "' AND time <= '" + tend + "'";
    msg.query = q;
    msg.trenddate = "Hallo";
}


return msg;

The resulting query on influxDB is

SELECT time, selfuse, totalsolar FROM power WHERE time >= '2021-10-31T13:29:52.738Z' AND time <= '2021-10-31T16:29:52.738Z'

That's where I was looking for, thanks you guys.
Although, I must say, it's a bit confusing with the quotes.....pfff...

Gr.
Henk

[Colin]

@henkkas

    let sel    = "SELECT time, selfuse, totalsolar FROM power ";
    let q      = sel + "WHERE time >= '" + tstart + "' AND time <= '" + tend + "'";
    msg.query = q;

You can make it simpler using template string literals

    msg.query = `SELECT time, selfuse, totalsolar FROM power WHERE time >= '${tstart}' AND time <= '${tend}'`

Are you sure you need the quotes round the timestamps?

[henkkas]

@Colin , your solution didn't work for me.
I get this error:

"Error: Error from InfluxDB: invalid operation: time and *influxql.StringLiteral are not compatible"

[henkkas]

@Colin , I am sorry. I correct myself. I only took part of the query.
After complete copy of statement it does work.
Now I have to learn about string literals....and the new kind of quote

Thanks Colin.