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

**URL:** <https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981>\
**Category:** General\
**Tags:** database\
**Created:** [30 October 2021 15:20 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981 "2021-10-30T15:20:47Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [30 October 2021 15:20 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/1 "2021-10-30T15:20:47Z")

</div>

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?

```auto
// 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);
}

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 October 2021 15:42 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/2 "2021-10-30T15:42:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [30 October 2021 15:46 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/3 "2021-10-30T15:46:09Z")

</div>

> [@henkkas](#):
>
> `let q = sel + "WHERE time >= tstart AND time <= tend";`

should be

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

```

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [31 October 2021 15:41 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/4 "2021-10-31T15:41:32Z")

</div>

@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"}

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [31 October 2021 15:52 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/5 "2021-10-31T15:52:47Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [31 October 2021 15:56 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/6 "2021-10-31T15:56:16Z")

</div>

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

```auto
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/#examples-11)

[https://docs.influxdata.com/influxdb/v1.8/query\_language/explore-data/#rfc3339\_like\_date\_time\_string](https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#rfc3339_like_date_time_string)

Also what are the context stored values?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [31 October 2021 15:58 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/7 "2021-10-31T15:58:54Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [31 October 2021 16:01 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/8 "2021-10-31T16:01:15Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [31 October 2021 16:02 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/9 "2021-10-31T16:02:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [31 October 2021 16:18 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/10 "2021-10-31T16:18:11Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [31 October 2021 17:53 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/11 "2021-10-31T17:53:16Z")

</div>

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

```auto
// 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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [31 October 2021 20:28 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/12 "2021-10-31T20:28:54Z")

</div>

> [@henkkas](#):
>
> ```auto
> 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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals)

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

```

Are you sure you need the quotes round the timestamps?

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [1 November 2021 10:26 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/13 "2021-11-01T10:26:23Z")

</div>

@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"

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [1 November 2021 10:31 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/14 "2021-11-01T10:31:39Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [15 November 2021 10:31 UTC](https://discourse.nodered.org/t/node-red-contrib-influxdb-select-with-absolute-time-range-error/52981/15 "2021-11-15T10:31:50Z")

</div>

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