GROUP BY time with relative time -

InfluxDB 1 InfluxQL
1

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

2

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:

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
3

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.