Group by time - why different grouping for mean() and integral()

Telegraf
1

Hi, I am using InfluxDB 1.8.4. I have a time series of power measurements (about every minute) and want to convert those into energy measurements (ie (kilo)Watts per day). I have that figured out, but I am struggling a bit with the GROUP BY time behavior of InfluxDB.

I would like to get results grouped by days going backward from now(); ie if I run the query at 15:00 hours, I would like the last group item returned to cover from previous day 15:01 to today 15:00.

The following query using mean() behaves just like that:
SELECT (mean("value") * 24) AS "value" FROM <myTable> WHERE time > now() - 30d GROUP BY time(1d) fill(null)

If, instead, I use integral()–which I need for proper results–the grouping is different. Rather than grouping by 24h blocks going backward, as desired, the grouping is by calendar days, eg the last group item returned will cover 00:00 to 15:00 (and the first item in the group will be similarly cut off):
SELECT (integral("value", 1h)) AS "value" FROM <myTable> WHERE time > now() - 30d GROUP BY time(1d) fill(null)

Does anyone have a suggestion how I could achieve the desired grouping behavior, ie the same behavior as for the query with mean()?

2

Hi, I am using InfluxDB 1.8.4. I have a time series of power measurements
(about every minute) and want to convert those into energy measurements
(ie (kilo)Watts per day). I have that figured out, but I am struggling a
bit with the GROUP BY time behavior of InfluxDB.

Energy is power times time - so WattSeconds, or kiloWattHours, or WattDays,
not Watts per Day.

If, instead, I use integral()–which I need for proper results–the
grouping is different. Rather than grouping by 24h blocks going backward,
as desired, the grouping is by calendar days, eg the last group item
returned will cover 00:00 to 15:00 (and the first item in the group will
be similarly cut off): SELECT (integral("value", 1h)) AS "value" FROM <myTable> WHERE time > now() - 30d GROUP BY time(1d) fill(null)

Does anyone have a suggestion how I could achieve the desired grouping
behavior, ie the same behavior as for the query with mean()?

Insetad of GROUP BY time(1d) try simply GROUP BY time(24h).

Antony.

3

Thank you for your response.

You are right, of course. So what I want is kWh over an entire day. And again, that is what the query is giving me, no problem there.

Yes, I had tried that before. Makes no difference, unfortunately. Still not the desired grouping behavior using integral().

4

Oh dear. Sorry, I thought that would group the data as you wanted.

I hope someone else has a better idea than mine.

Antony.

5

Hello @kstuken,
That sounds like a bug? Can you file an issue? I’ll also share with the InfluxDB team.
Thank you

6

Thank you, am considering it. But looking a bit deeper at this, there seems to be a number of issues at play here. No 3) below looks like a straightforward bug to me. No 2) also looks buggy, or at least very non-intuitive…

Three things I figured out since my initial post:

1) I realized that the grouping behavior is actually consistent across aggregation functions, ie integral(), mean() and count() all cut off at the end of the calendar day. (Eg right now, as local time crossed from just before to just past midnight, the last value in the result set from both integral() and count() grouping fell down to effectively zero and will now increase as the day progresses.) In my initial issue description above I had thought that mean() behaved differently because I multiplied the mean query by *24, which gave me reasonable-looking data as soon as there was a little bit of data for the current day.

2) Still, at minimum this looks like very counter-intuitive behavior. What do I have to do to get time grouping of continuous 24h blocks that are not aligned with calendar day breaks? It gets weirder when I try this:
SELECT (count("value")) AS "value" FROM "<myTable>" WHERE time > now() - 690h GROUP BY time(23h) fill(null) tz('Europe/Berlin')
In my view that should give me a result set of 30 grouped values (690h / 23h). But no, 31 values in the result set, somehow the first and last values in the result set seem not be for full 23h intervals.

3) It becomes fully bizarre when timezone is involved.
SELECT (count("value")) AS "value" FROM "<myTable>" WHERE time > now() - 720h GROUP BY time(24h) fill(null) tz('Europe/Berlin')
When I run this query between midnight and 1am local time, rather than getting a result set of 30 values (ie 720h / 24h), I do get a result set of 32 values. That is just plain wrong. Even if the first and last result value are for significantly less than 24h, the remaining 30 values should be for the full 24h, and hence either the query is executed for way more than the 720h limit I set, or there is some data duplication here. (past 1am I am getting 31 results for that query).

7

I have filed a bug here