New year problem: duplicate bins when using time span over 1 year

Grafana Bar Chart Panel
1

If have a simple query, that does work fine until the query has a time span over two different years.

I want to see how often a specific temperature was measured (in hours).

Good result:

  • “Last 2 days”
  • Any time span only in the last year, any time span only in the new year

Bad result:

  • A query which contains a time span from 2025 and 2026 (e.g. “last 7 days”)

Here is the query:

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r["_measurement"] == "sensor.wetterstation_outdoor_temperature" and
    r["_field"] == "value"
  )
  |> aggregateWindow(every: 60m, fn: mean, createEmpty: false)
  |> filter(fn: (r) => r["_value"] >= -15.0 and r["_value"] <= 15.0)
  |> histogram(bins: linearBins(start: -15.0, width: 1.0, count: 31, infinity: false))
  |> difference(columns: ["_value"], nonNegative: true)
  |> map(fn: (r) => ({ bin: r.le, _value: r._value }))
  |> yield(name: "histogram")

Here the results:

Time span over one year only:

Bildschirmfoto 2025-07-09 um 10.37.08
Bildschirmfoto 2025-07-09 um 10.37.081156×594 29.3 KB

Time span over two years (this screenshot uses “Last 7 Days”):

Bildschirmfoto 2026-01-06 um 00.03.18
Bildschirmfoto 2026-01-06 um 00.03.181674×582 31.5 KB

My bins for this graph look like “15 15 14 14 13 13 …” when I use “table view” and sort by bin value.

I already asked 3 different AI’s what could be wrong, but they have no clue.

Does anybody know why the temperature bins are duplicated on the x axis and how to solve it?

2

The problem is, that the data is stored in differend shards in influxdb (new year). The solution looks like this:

import "math"

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r["_measurement"] == "sensor.wetterstation_outdoor_temperature" and
    r["_field"] == "value"
  )
  |> aggregateWindow(every: 60m, fn: mean, createEmpty: false)
  |> drop(columns: ["_time", "_measurement", "_field"])
  |> filter(fn: (r) => r["_value"] >= -15.0 and r["_value"] <= 15.0)
  |> histogram(bins: linearBins(start: -15.0, width: 1.0, count: 31, infinity: false))
  // Convert to frequencies per shard/table
  |> difference(columns: ["_value"], nonNegative: true)
  |> filter(fn: (r) => exists r._value)
  // Now merge across shards: group by bin and sum frequencies
  |> group(columns: ["le"])
  |> sum(column: "_value")
  // Ungroup, sort, and format as strings for clean bar chart labels
  |> group()
  |> sort(columns: ["le"])
  |> map(fn: (r) => ({ bin: string(v: math.round(x: r.le)), _value: r._value }))
  |> keep(columns: ["bin", "_value"])
  |> yield(name: "histogram")
1 Like