GROUP BY time with relative time -

Hi all, I have faced with the problem in case of using aggregation (mean) and related time (now). The description of the InfluxDB - table with field - temperature, some tags (machine_id, zone_id), data is written with interval 640 ms. Exampe of dataset that written in the InfluxDB is below:

{'time': '2021-07-12T11:34:26.451000Z', 'zone': '002', 'value': 200.04},
{'time': '2021-07-12T11:34:27.091000Z', 'zone': '002', 'value': 200.04},
{'time': '2021-07-12T11:34:27.731000Z', 'zone': '002', 'value': 199.95},

For visualisation of situations, when device is not used, I have used a aggregation - mean(value) above with GROUP BY time(1000ms) and fill(0). This query is shown below and it is used as subqery.

"SELECT mean(value) AS mean_value FROM temperature WHERE time > now() - 20m AND time <= now() - 15s GROUP BY time(1000ms) fill(0) "

I’ve thought that time(1000ms) will provide the same values as it was in original dataset, but with 0 values for time, when no data was stored in the database. Actually, I have received so called “border case”. For example, we have started query in time 2021-07-12T11:54:26.851000Z.
now() - 20m it means 11:34:26.851000Z - this is a starting time for query the data from DB. The time averaging function return the first point with time 2021-07-12T11:34:26.000000Z, but the mean will average the points in the interval from 11:34:26.000000Z to 11:34:27.000000Z, and point with timestamp 2021-07-12T11:34:26.451000Z will not be queried, because it is BEFORE the now() - 20m!

So, we will have query result, starting with:

{'time': '2021-07-12T11:34:26Z', 'mean_value': 0.0},
{'time': '2021-07-12T11:34:27Z', 'mean_value': 200.0},

and we are loosing one point at the start (or at the end) of our interval for averaging… In case if we are not using fil() it is not a problem - it will be just missed point, but if you need to fill empty spaces - it is a problem.

The question - is it possible in InfluxQL provide some processing of function now, e.g., to take into account only hours, minutes and seconds? Like now(), but started from seconds, or now - started from minutes etc. I’ve read about offset, but looks like it is not a solution (I’ve tried it).

As far as I know, Time rounding (to seconds/minutes/etc) is just not possible in the InfluxQL language itself.

Flux has some options to do so, see:
https://docs.influxdata.com/influxdb/cloud/query-data/flux/manipulate-timestamps/#normalize-irregular-timestamps

Another way is to use absolute time, therefore actual UNIX timestamp or just the human-readable timestamp (RFC 3339 standard) values in your time filter.
Doing so allows you to round timestamps however you like.

1 Like

Thank you for the answer. Actually, I just deleted the first and the last points later in Python, where the data have been processed before the visualization. As for absolute time I’m using it, but in the “live monitoring” mode I need to use just 20min interval up to now.

I’m not ready for switching to Flux language, but looks like it provides much more possibilities, so I will try to use it in the future.