Build dynamic query string for InfluxDB

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.

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.

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.

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.

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

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.

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.

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

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

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

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

You should be able to get it to use your local timezone using something like
GROUP BY time(1d) TZ('America/Chicago')
https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#the-time-zone-clause

1 Like

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?

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

@borpin did you get this working?

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

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

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.

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

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?