Aggregate by day so far

Fluxlang
1

Hi, I’m using InfluxDB2 (flux) and Grafana to generate dashboards of metrics from my reef aquarium and I love the functionality.
Something that I haven’t been able to figure out is how to display bar charts that show a sum aggregate by day, based on natural days, not by every 24h.
The practical example is that my dashboards show a time range of the last 7 or 14 days I use many line charts where I need detailed information (like when I track temperature). But in some cases I just want to see how much solution I have dosed per day regardless of the time I see the chart. Lets pretend that I dose 1ml every hour. If I check the dashboard at 8:00am, I would like to see that today I have dosed 8ml so far and for the past days I have dosed 24ml.
Instead what I’m seeing is what I have dosed in the past 24 hours so the chart would show 24ml dosed in the past period instead of 8ml.
Here is the function that I’m using:
|> aggregateWindow(every: 24h, fn: sum, createEmpty: false)

I have thought that a workaround would be to count the hours that have passed today and drop them at the beginning of the time period so that my periods always start at 00:00 but I don’t really know how to do that.
I think I could also adjust my |> range(start: v.timeRangeStart, stop: v.timeRangeStop) to start at 00:00 but I like to have the flexibility to define start and end times to analyze data so I’d prefer not to change that.

I hope my explanation and example were easy to follow and I’d appreciate any recommendations or guidance.
Thank you

2

@miguel Another method you could use is to truncate the time column to days, add _time to the group key so tables are grouped by common day, apply the sum aggregation, then remove _time from the group key.

data
    |> truncateTimeColumn(unit: 1d)
    |> group(columns: ["_value"], mode: "except")
    |> sum()
    |> group(columns: ["_value", "_time"], mode: "except")