# Build dynamic query string for InfluxDB

**URL:** <https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354>\
**Category:** General\
**Created:** [20 February 2021 16:11 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354 "2021-02-20T16:11:16Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [20 February 2021 16:11 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/1 "2021-02-20T16:11:17Z")

</div>

I'd like to build a dynamic query string to pass to InfluxDB node from an Inject node.

The standard string

```auto
SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 10m

```

works fine, but I'd like to make the **time** element dynamic such that I can query from midnight (I can't find a way to do this directly with the builtin InfluxDB functions)

I am thinking JSONata can probably help, but I cannot find how to build a string (the quotes are required).

[edit]  
I could do it in a function node, but wondered if there is an easier way.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [20 February 2021 16:37 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/2 "2021-02-20T16:37:44Z")

</div>

Grafana really is your friend here because you can easily create a query with point and click and then use the query inspector to see the InfluxDB query:

```auto
SELECT mean("value") FROM "one_week"."environment" WHERE ("type" = 'humidity') AND time >= now() - 1h GROUP BY time(1m), "location", "type" fill(linear)

```

Here is a query that Grafana gave when I asked it to show "Today":

```auto
SELECT mean("value") FROM "one_week"."environment" WHERE ("type" = 'humidity') AND time >= 1613779200000ms and time <= 1613865599999ms GROUP BY time(2m), "location", "type" fill(previous)

```

So you can see that it has dynamically added the ms values for midnight and now.

---

<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:** [20 February 2021 17:27 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/3 "2021-02-20T17:27:38Z")

</div>

If what you don't know how to do is to determine the time to enter for midnight of today then if you take the current time in ms `let now = new Date().getTime()` and divide that by the number of msec in a day, truncate it to an integer and multiply it by the number of msec in a day again you will end up with the millisecond timestamp for the start of the day, GMT. If you need that in local time then adjust by your timezone offset.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [20 February 2021 18:43 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/4 "2021-02-20T18:43:46Z")

</div>

> [@TotallyInformation](#):
>
> Grafana really is your friend here because you can easily create a query with point and click and then use the query inspector to see the InfluxDB query:

Thanks, but I'm doing this in NR to create a sensor in HA so Grafana isn't really the right tool.

This is more about how do I build a string in JSONata into which I can insert a calculation to give midnight. I am expecting to create the string in the form of

```auto
"SELECT \"x\" FROM y WHERE X AND time " & $a_datecalc

```

> [@Colin](#):
>
> If what you don't know how to do is to determine the time to enter for midnight

I can do that bit (actually I'd use Modulo to do it) - it is the building of the resultant string that is currently defeating me.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [20 February 2021 18:59 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/5 "2021-02-20T18:59:38Z")

</div>

Solved it.

The answer is actually to use a 'feature' of influx DB as explained here [Sum last 10 minutes of data - #2 by Giovanni\_Luisotto - InfluxData Community](https://community.influxdata.com/t/sum-last-10-minutes-of-data/18453/2?u=borpin)

If I do a query thus

```auto
SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 1d Group by time(1d)

```

I get the following data output

```auto
[{
	"time": "2021-02-19T00:00:00.000Z",
	"sum_diff_rain_mm": 0.3000000000000007
}, {
	"time": "2021-02-20T00:00:00.000Z",
	"sum_diff_rain_mm": 6.899999999999999
}]

```

Whilst this gives me 2 time periods, one is the ~~previous full 24Hr~~ data from `now() - 1d` to midnight (the 'time' is always the period start time), and the second is the data since midnight.

Perfect.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [20 February 2021 21:18 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/6 "2021-02-20T21:18:29Z")

</div>

> [@borpin](#):
>
> Thanks, but I'm doing this in NR to create a sensor in HA so Grafana isn't really the right tool.

I didn't mean that you needed to switch to it wholesale 😁

Just use it as a discovery tool for InfluxDB queries. It doesn't really take up that many resources. Though if you are short, simply install it and then leave it stopped.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [20 February 2021 21:20 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/7 "2021-02-20T21:20:26Z")

</div>

> [@borpin](#):
>
> If I do a query thus

I'm confused. You said you wanted to query from midnight? The one you have written queries from 24 hours ago, that was in my first example.

---

<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:** [20 February 2021 21:32 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/8 "2021-02-20T21:32:58Z")

</div>

I think the group by groups on whole intervals, if you see what I mean, so group by 1d groups on whole days. The query then gives two results, the first is for yesterday and the second is today, starting at midnight.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [21 February 2021 08:07 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/9 "2021-02-21T08:07:34Z")

</div>

> [@Colin](#):
>
> I think the group by groups on whole intervals,

Yes that is exactly it (I discovered). What I said earlier was not completely correct. If I query from `now() -1d` and group on `1d` I get 2 groups

1. Data from `now() - 1d` to midnight
2. Data from Midnight to `now()`

[Edit]  
However, this is of course UTC periods....

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [21 February 2021 10:40 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/10 "2021-02-21T10:40:47Z")

</div>

To get local periods, simply calculate the ms timestamps needed in node-red and use those instead as in the 2nd example I gave.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [21 February 2021 11:04 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/11 "2021-02-21T11:04:43Z")

</div>

Yes but I can't build the string (which was where I started).

---

<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:** [21 February 2021 11:14 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/12 "2021-02-21T11:14:48Z")

</div>

> [@borpin](#):
>
> However, this is of course UTC periods

You should be able to get it to use your local timezone using something like  
`GROUP BY time(1d) TZ('America/Chicago')`

> **[Explore data using InfluxQL | InfluxDB OSS v1 Documentation](https://docs.influxdata.com/influxdb/v1/query_language/explore-data/)**
>
> Explore time series data using InfluxData’s SQL-like query language. Understand how to use the SELECT statement to query data from measurements, tags, and fields.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [21 February 2021 11:34 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/13 "2021-02-21T11:34:43Z")

</div>

I'm getting an error with `tz('Europe/London')`

```auto
Error: A 400 Bad Request error occurred: {"error":"error parsing query: tz must be a function call"}

```

It works in Chronograph.

Any ideas?

---

<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:** [21 February 2021 12:11 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/14 "2021-02-21T12:11:06Z")

</div>

Can you post the full query you are using please, copy/paste it to make sure no typos

---

<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:** [25 February 2021 11:50 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/15 "2021-02-25T11:50:30Z")

</div>

@borpin did you get this working?

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [25 February 2021 13:21 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/16 "2021-02-25T13:21:41Z")

</div>

Oh sorry, yes, not sure what the issue was.

```auto
SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 1d GROUP BY time(1d) tz('America/Chicago')

```

```auto
[{
	"time": "2021-02-24T06:00:00.000Z",
	"sum_diff_rain_mm": 0.8999999999999986
}, {
	"time": "2021-02-25T06:00:00.000Z",
	"sum_diff_rain_mm": 0.6000000000000014
}]

```

~~Interestingly, I'm not sure that date string is ISO compliant. The Z on the end is not correct as there is a "+06:00". I just noticed there is no '+' so it is definitely not compliant!!!!~~

~~I think that must be added by the NR node. The query from the command line;~~

```auto
> SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 1d GROUP BY time(1d) tz('America/Chicago')
name: emon_input
time sum_diff_rain_mm
---- ----------------
1614146400000000000 0.8999999999999986
1614232800000000000 0.6000000000000014
> SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 1d GROUP BY time(1d)
name: emon_input
time sum_diff_rain_mm
---- ----------------
1614124800000000000 0.8999999999999986
1614211200000000000 0.6000000000000014

```

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [25 February 2021 13:26 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/17 "2021-02-25T13:26:06Z")

</div>

> [@borpin](#):
>
> but I can't build the string (which was where I started).

I didn't get an answer to the original question - how can I build a dynamic query string.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [25 February 2021 13:46 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/18 "2021-02-25T13:46:35Z")

</div>

Actually, I think you did. 🙂

I think that one of my answers was for you to create a ms timestamp for midnight and append that to the `time` part of the query with a trailing `ms` text. I didn't write that in the answer directly but the format was in the example and you already had most of the JSONata.

---

<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:** [25 February 2021 13:50 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/19 "2021-02-25T13:50:48Z")

</div>

Or use a template node, that may well be the easiest.

---

<div class="post-metadata">

**Author:** ![borpin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/borpin/32/1765_2.png) [@borpin](https://discourse.nodered.org/u/borpin)\
**Post date:** [25 February 2021 14:29 UTC](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354/20 "2021-02-25T14:29:36Z")

</div>

I did try in it's most basic form and it didn't work. Not sure what I have just tried differently but I can get a string built now which works in a JSONata field.

```auto
"SELECT sum(\"diff_rain_mm\") AS \"sum_diff_rain_mm\" FROM \"telegraf\".\"autogen\".\"emon_input\" WHERE time > - 1d GROUP BY time(1d) tz('America\/Chicago')"

```

I have looked around and cannot find anything about building a bigger expression. How do I transfer the variable into the final string?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/4/94481531acad59006c089dd9ce099d817b39053c.png)

Is there a good resource for using the expression builder?

[Next page](https://discourse.nodered.org/t/build-dynamic-query-string-for-influxdb/41354.md?page=2)