GROUP by time looking "backwards" from a specific timestamp

InfluxDB 2
,
1

Dear experts,
I’m storing data from a bunch of sensors in an InfluxDB database (to monitor them in Grafana).
Besides the actual sensor readings I also want to monitor if the sensors are sending regularly new values, since the sensors use a wireless connection that may have problems.
Since I know the interval at which the sensors are supposed to send data, my idea was to count the sensor entries in the database for a given time frame.
More hands on: when a sensor is supposed to send a new value each 5 min I expect 12 sensor values per hour.

I tried this query:
SELECT count("temperature") FROM "room_temp" WHERE ("device_id"::tag = 'xyz') GROUP BY time(60m)

That’s not too bad but has two issues:

  • It looks kind of ahead.
    Example: now it’s 10:18. Hence I have received a sensor value at 10:00, 10:05, 10:10, 10:15 → the count is 4. I have a plotted point in my Grafana visualization with the time stamp 10:00 with the value 4. Looks at 1st glance as if there is an issue, since I expect 12 sensor values per hour, not 4.
    Only at 10:55 I will have the expected number of 12 sensor readings.
    What I want achieve is that the queried value with time stamp 10:00 looks back, i.e counts the received sensor values between 9:00 and 9:59.
    I tried to use an offset_interval → ... GROUP BY time(60m, -60m), but that didn’t change anything.

  • The example above is not entirely realistic, since the sensor are comparably simple, they are not aware of the actual time. The only can keep their sending interval (e.g. 5 min). Whenever you switch them on, they will send their 1st sensor value and the next one 5 min later. And even this 5 min are due to a few seconds deviation.
    Again an example: sensor value received at 10:04:59. Next is received after 5:02 at 10:10:01, then 10:15:03 and so on. The 12th value in this row will be received at 11:00:21. Therefore the above query will return only 11 sensor values between 10:00:00 and 11:00:00. Looks like I have lost one sensor value, but it’s not the case.
    To make the query even better I would like to look 60 min back, but not from the full hour but from the timestamp of each sensor value entry in the influxdb database.

Is there a way to achieve that? Preferably in InfluxQL, since I have avoided to look into FLUX up to now.

2

Hello @cortlieb,
If you want to look back in time use the where clause

for example

SELECT * FROM "h2o_feet" WHERE time > now() - 7d and time < now() - 5d
3

Unfortunately there isn’t a way to look at time relative to the last timestamp with influxql.
I might suggest using another processing tool like quix or bytewax.

4

Hi Anaisdg,
thank you for your replies!

That’s not exactly what I wanted. I want took look back in terms of GROUP BY time.
Example: when it is now 9:30 I would like to count datapoints from 8:30 to 9:30. But GROUP BY time(60m) seems to look at each 60 min at full hour, independent from the current time. In my example at 9:30 I will get the count from 9:00 to 10:00.
I can adjust that with the offset_interval, but not related to the current time, if I understood everything correctly.

Of course it’s more fun to have a solution, but it is also good to know what is not possible. Then I don’t need to invest more time in looking in that direction.
And thank you for the hint to the Python libraries, I will look into that.

1 Like
5

@cortlieb Ah I see. Take a look at the options for grouping by time here:

:slight_smile:
I belive you want time_interval

6

@Anaisdg , thank you for your reply!

But isn’t that what I’m using (see my 1st post in this thread) :thinking:?

This leads to the behavior I described: