Sum last 10 minutes of data

#1

I realise there are a couple of posts like this, but none seem to actually provide an answer.

I have a data source writing a data value every 48 seconds to InfluxDB database.

I want to sum the most recent 10 minutes worth of values.

I have the following query submitted via Node-Red

SELECT sum("diff_rain_mm") AS "sum_diff_rain_mm" FROM "telegraf"."autogen"."emon_input" WHERE time > now() - 30m GROUP BY time(10m) FILL(0)

I am deliberately doing more than 10 minutes to see what happens :slight_smile:. Clearly the query is doing what I ask.

  • The data is always returned in round 10 minute chunks whereas I want to sum the values from 17:22 → 17:32.
  • It does not actually sum the points within that time range - they are 0.3 steps.

How can I achieve this?

{
	"_msgid": "fae94d7a.291a1",
	"query": "SELECT sum(\"diff_rain_mm\") AS \"sum_diff_rain_mm\" FROM \"telegraf\".\"autogen\".\"emon_input\" WHERE time > now() - 30m GROUP BY time(10m) FILL(0)",
	"topic": "Rain",
	"time": "2021-02-19T17:32:28.934Z",
	"payload": [{
		"time": "2021-02-19T17:00:00.000Z",
		"sum_diff_rain_mm": 0
	}, {
		"time": "2021-02-19T17:10:00.000Z",
		"sum_diff_rain_mm": 0
	}, {
		"time": "2021-02-19T17:20:00.000Z",
		"sum_diff_rain_mm": 0
	}, {
		"time": "2021-02-19T17:30:00.000Z",
		"sum_diff_rain_mm": 0.29999999999999893
	}]
}
#2

Not sure about what you expect but I’ll guess it’s the following:

Let’s say that now it’s 22:24 and I fetch the last 30min of data
I get data ranging from 21:54 to 22:24.
I use GROUP BY time(10m) to group the data into intervals (each interval is grouped in the timestamp at the beginning of its time window)
Expectation:
having 3 groups
21:54, 22:04, 22:14
Reality:
I end up having 4 Groups
22:50, 21:00, 21:10, 21:20

When using “Group by time()” this is the behavior and you can’t change it, I’ve kind of thought about it only now, and I think that’s to make the results more standard and readable as using a non-fixed time reference will make comparisons, matching and other stuff a lot harder

On the good side, you don’t need it to satisfy your requirement.
You just need to get the last 10m of data and sum them, so just use

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

Note: FILL(0) has been removed as it won’t change the query output, if there are no points in the time interval you will get no output with or without it, if you need to force a 0 in there just use +0 sum("diff_rain_mm")+0

1 Like
#3

@Giovanni_Luisotto is there a time statement that will get the data since midnight?

#4

To answer my own question, I use the feature as explained by @Giovanni_Luisotto.

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
}]

[Edited as per subsequent message]

This gives me data for 2 time periods, one is the previous full 24Hr one is for data found from the start point (now () - 1d) to midnight and the second is the data since midnight.

1 Like
#5

I just want to point out that the first set does not actually contain the full previous 24h, but only the interval interested by the time filter, i.e.
It’s 2021-02-20 00:45
I get now()- 1d, therefore the time range spans from 2021-02-19 00:45 to 2021-02-20 00:45.
By grouping by day I obtain data grouped at 00:00 of each day, therefore:
2021-02-19 00:00 → 00:45 to 23:59
2021-02-20 00:00 → 00:00 to 00:45

Note that for the first group (2021-02-19 00:00), data from 00:00 to 00:45 won’t be included even if the the resulting point is placed at 00:00.
If grouping by days months etc is what you need, you’ll have to store those data as tags (there the telegraf date processor for this)

1 Like
#6

OK, thanks for that :slight_smile:

Thanks for that. I’m new to Telegraf but the raw data is being loaded by Telegraf in the first place via an MQTT Consumer.

How would I integrate the date processor?

I am using the name_override option in the mqtt_consumer to drop different Telegraf input sources into different measurements.

Can the date processor be limited to certain measurements?

[edit]
When grouping by day, is the day always a UTC day? Is there anyway to get a day for a specific timezone?

#7

You can do a lot of stuff with telegraf, filtering included (even for processing/editing of metrics).
In your case I think namepass will do the trick.
About the date in a specific timezone, yes that’s supported just look at the docs

#8

Yes I am gradually finding that :grinning:

I have raised a separate question about the structure of my MQTT data as I think if I did this as my data is currently structured, it would add this tag to all my MQTT data which I do not want - just the specific topic. Collecting metrics via mqtt_consumer

Leaving that question aside for the minute, if I want to add tags Day, Month, Year - do I need to create 3 separate processors?

[[processors.date]]
  ## New tag to create
  tag_key = "month"
  date_format = "Jan"

[[processors.date]]
  ## New tag to create
  tag_key = "day"
  date_format = "2"

[[processors.date]]
  ## New tag to create
  tag_key = "year"
  date_format = "2006"
#9

Yes you got it right, you need 3 different processors

1 Like