Search code examples
aggregateinfluxdb

How to eliminate last residual data point in InfluxDB aggregatewindow query

I have a query question with InfluxDB;

I am trying to aggregate the data per day and get the medians. The dates are truncated to the start of the day (00:00:000) But, the query returns one more last data which is not truncated to the start of the day; How can I truncate the last data point’s time to the start of the day / or ignore the last value?

My query:

from(bucket: "metric")
  |> range(start: -30d, stop: 0d)
  |> filter(fn: (r) => r["_measurement"] == "metric")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["metric"] == "SOME_METRIC")
  |> aggregateWindow(every: 1d, fn: median, createEmpty: true)
  |> yield(name: "median")

I added the query results and the text explains my situation

What I am trying to get is points as:

(Lets say today is 17.02.2022);

15.02.2022 00:00:00:000 - 16.02.2022 00:00:00:000 - 17.02.2022 00:00:00:000

But I got

15.02.2022 00:00:00:000 - 16.02.2022 00:00:00:000 - 17.02.2022 00:00:00:000 - 17.02.2022 05:30:27:437

Thanks in advance.

enter image description here

enter image description here

Solution

  • Ok, I figured out that I must give exact dates instead of -d notation in the time range. In addition, the dates must be truncated suitable with the aggregate window.

    from(bucket: "metric")
  |> range(start: 2022-01-16T00:00:00Z, stop: 2022-02-17T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "metric")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["metric"] == "SOME_METRIC")
  |> aggregateWindow(every: 1d, fn: median, createEmpty: true)
  |> yield(name: "median")