Water meter data visualisation

InfluxDB 2
1

Hi! I have water meter data, but the data is being logged only during “business hours”, so it starts around 4am, stops around 5pm and is not logged on weekend.
I’d like to have a graph with daily water consumption. For now, I have something like

import "date"

from(bucket: "ha")
  |> range(start: date.truncate(t: v.timeRangeStart, unit: 1d), stop: date.add(d: 1d, to: date.truncate(t: v.timeRangeStop, unit: 1d)))
  |> filter(fn: (r) => r["entity_id"] == "meter_cold")
  |> filter(fn: (r) => r["_field"] == "value")
  |> timeShift(duration: -2d)
  |> aggregateWindow(every: 1d, fn: first)
  |> difference()

This more or less works, but:

  • I’d like to see “today’s” consumption (since the first measurement until now),
  • that would be great, to distribute the weekend’s data evenly across Friday, Saturday and Sunday.

I’m completely stuck with the 2 points, can you help?

2

Cześć Łukasz!

If the equipment is shut down every night from 17:01 to 03:59 the next day, one can use the Flux function hourSelection() which filters rows by time values in a specified hour range.

|> hourSelection(start: 4, stop: 17)

So your first range() function does not need to have all that extra stuff. Just picking a start & stop will work and the hours of the day will be filtered based on the hourSelection function.

Re: the days of the week to include/exclude, not sure if that functionality exists in Flux. My guess is that it does, but will try to look around, or maybe someone else will chime in.

3

Maybe I should add a bit more context here.

  1. I don’t know when the first data arrives. It usually is around 4am, but it might be at 7am as well.
  2. I consider the first reading of a day as the final state of the previous day. If I don’t do that, I will add evening consumption to the next day.
4

Hi Łukasz,

Sorry for the delay. I am not completely sure this is what you are seeking, but I think this should give you the daily consumption. If you set the time selector for the past 1 or 3 or 7 days, you should see a total value for each day.

from(bucket: "ha")
  |> range(start: v.timeRangeStart, stop: today())
  |> filter(fn: (r) => r["entity_id"] == "meter_cold")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)
  |> yield(name: "daily")

The above does not care whether the water flow started at 12:01 AM or 4:15 AM or 7 AM, nor what time it stopped. It’s just summing up the values being reported during each day.

In order to distribute the weekend’s data evenly across Friday, Saturday and Sunday, I think you would have to create some sort of custom function to extract those 3 days of the week (which Flux can do) and then do the math there, but that is beyond my knowledge level.

1 Like