Flux Time Filtering for 24 hours over multiple days

[davidmartin1408]

I am trying to filter data using flux for the last 7 days full days (00:00:00 to 23:59:59). I then use aggretateWindow to sum by day. The problem is that all time references are relative to now(). this means the data for the period 7 days ago changes depending on time of the day. Is there an option for the range to be absolute and not relative?

data = from(bucket: “home”)
|> range(start: -7d, stop:-1d)
|> filter(fn: (r) => r[“_field”] == “BATTERY_CHARGE” or r[“_field”] == “BATTERY_DISCHARGE”)
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({r with “Battery_Discharge”: r.BATTERY_DISCHARGE / 12.0}))
|> map(fn: (r) => ({r with “Battery_Charge”: r.BATTERY_CHARGE / 12.0}))
|> keep(columns: [“_time”, “Battery_Charge”, “Battery_Discharge”])
Discharge_Sum = data
|> aggregateWindow(column: “Battery_Discharge”, every: 1d, offset: -8h, fn: sum)
Charge_Sum = data
|> aggregateWindow(column: “Battery_Charge”, every: 1d, offset: -8h, fn: sum)
union(tables: [Discharge_Sum, Charge_Sum])
|> yield(name: “result”)
|> group(columns: [“_time”], mode: “by”)
|> sort(columns: [“Battery_Charge”])
|> fill(column: “Battery_Discharge”, usePrevious: true)
|> tail(n: 1)
|> group()
|> drop(fn: (column) => column =~ /^(_start|_stop|_measurement|location|region)/)
|> rename(columns: {Battery_Discharge: “Battery Discharge”, Battery_Charge: “Battery Charge”})

[jangaraj]

Always check doc first range() function | Flux 0.x Documentation You will find there that you can use a relative duration, absolute time, or integer (Unix timestamp in seconds) for start/stop range parameters. So answer is yes.

[davidmartin1408]

Hi
I have been through the documentation but now of the options on the range function pick up whole days of data relative to the today.
I have been trying to work out how to strip the time off the start value so it is just 00:00:00 but no joy so far.
Thanks

[grant2]

I do something similar (comparing last week’s data with this week’s data, where it resets on Sunday morning, I believe), but it sounds like you want the panel to change once per day and reflect the last 7 days worth of data, correct?

settings for the “42” panel above:

settings for the “0” panel above:

I think you can solve this by playing with the Relative time and Time shift fields shown above. There is a good thread linked below in case you have not seen it yet.

How to shift date of data and how to set per-graph time range?

Hello everyone I am new to Grafana and am loving it so far. Relatively easy to use and get into and looks great. There are two small issues I am facing. How can I shift the data by -24 hours? I have data which is being reported as “consumption yesterday”, so effectively I am seeing the power consumption of the previous day (this makes sense because live daily consumption is not much good given that you won’t have all values until midnight). I now want to shift the data so it …

[davidmartin1408]

I am trying to create this

but the time reference is relative to now. As you can see in the bar chart there is no battery charging recorded in the first time period. this is because charging was finished at this time (time now 7:20pm).
I understand the time shift function in Grafana with the -7d/d representing whole days 7 days ago. What I can’t work out is how to do it in flux. This creates an error in the syntax
|> range(start: -7d/d, stop:-1d/d)
whereas
|> range(start: -7d, stop:-1d) work but backwards from now()

[grant2]

@davidmartin1408

Almost 3 months late, but if you are still searching for a solution, I believe it can be found here.