Build dynamic query string for InfluxDB

General
1

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

The standard string

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.

2

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:

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

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.

3

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.

4

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

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

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.

5

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

If I do a query thus

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

[{
	"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.

1 Like
6

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

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.

7

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.

8

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.

1 Like
9

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

1 Like
10

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

11

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

12

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

1 Like
13

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

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

It works in Chronograph.

Any ideas?

14

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

15

@borpin did you get this working?

16

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

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')

[{
	"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;

> 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
17

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

18

Actually, I think you did. :slight_smile:

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.

19

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

20

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.

"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

Is there a good resource for using the expression builder?