Hello. I’ve been playing with influx for the last week but I have been unable to make sure when I’m doing aggregations I get a value in the current time instead of getting values based on the presets.
This is my starting query:
SELECT time, mean(value) from temperature where time >= ‘2019-11-03’ and time <= ‘2019-11-06T02:51:18Z’ group by time(5h) order by time desc
top result:
"2019-11-06T01:00:00Z", 38.379880819234764
If I change that query with the following, I get the desired result, which is getting a data point now, and not data points in the presets.
SELECT time, mean(value) from temperature where time >= ‘2019-11-03’ and time <= ‘2019-11-06T02:51:18Z’ group by time(5h, -189m) order by time desc
desired top timestamp in top result:
“2019-11-06T02:51:00Z”, 38.379880819234764
(Im aware that the aggregation might be a null because influx does aggregation after the timestamp and not before)
My question is how to calculate the -189 value (Is this possible with the date ranges and the ‘bucket’ time range, in this case 5h?). I’ve been trying many things with the date ranges, modulo and such but haven’t been able to get what I’m looking for.
I also found this which looked like I could have used last() to achieve what I was looking for, but I tried and it threw and error saying that only now() was supported
I wonder if you properly understand what you’re trying to do. (Or maybe I don’t!)
The GROUP BY function is intended to generate multiple measurements from a range of input measurements, and does this by assigning each input measurement to a “bucket” based on the input measurement’s timestamp. The generated measurements will have timestamps equivalent to the start time of each bucket.
It looks like you want the generated measurements to have the end time of each bucket, except for the most recent measurement which should have a timestamp of ‘now’. Is that really what you want? I suspect it would only be useful to do it that way if you can guarantee that your query runs at the same time(s) every day.
If you just want the average of the most recent 5 hours worth of measurements why not just omit the GROUP BY clause and instead specify a start time and and end time? Your application will have to ignore the timestamp generated by InfluxDB and substitute whatever is appropriate.
I don’t think you got my idea right after reading the second paragraph you wrote. I have already sorted that, I was just putting that in context.
What I want to achieve is that the buckets that are created have one bucket at now and then 5 hours ago, and before that 5 hours ago (and so on). Instead of using the bucket presets. @JeremySTX
@JeremySTX
It is on demand, Could be any time(Usually on weekly 1hr buckets, but could be any time range and any bucket), thats why I want to calculate the second parameter of the group by with the time ranges and the bucket size. I’ve tried some stuff as I mentioned but I didn’t get it right.
