Energy daily/monthly usage with two tariffs and displayed in grafana

InfluxDB 2
1

Hi, New to influx and im trying to display energy usage per day(7day view) and monthly total.

My data is cumulative

0 energy Canal_Side_Supply_KWH_Value 76722665 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T07:00:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76722780.66666667 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T07:30:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76722997.86666666 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T08:00:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76723218.53333333 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T08:30:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76723440.46666667 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T09:00:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76723667.2 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T09:30:00.000Z U_Energy_Flux
0 energy Canal_Side_Supply_KWH_Value 76723893.46666667 2022-06-03T06:57:56.117Z 2022-06-10T06:57:56.117Z 2022-06-03T10:00:00.000Z U_Energy_Flux

What im trying to achive is the following with enegy usage for each time window ie 00 - 07 tariff 1 and 07 - 00 tariff 2

image
image1398Ă—449 27.7 KB

what im struggling with is to display the difference between a set time rage that spans over multiple days

this is what i have so far, this is one query for 00-07 and i have another one for 07-00

import “date”
from(bucket: “utilities”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “energy”)
|> filter(fn: (r) => r[“LogGroup”] == “U_Energy_Flux”)
|> filter(fn: (r) => r[“_field”] == “Canal_Side_Supply_KWH_Value”)
|> filter(fn: (r) => {hour = date.hour(t: r._time) return hour >= 0 or hour < 7})
|> aggregateWindow(every: 1d, fn: last, createEmpty: false)
|> difference(nonNegative: false, columns: [“_value”])

i think i need to eithe use max - min or map to condition my data but thats where im failing

any help would be apriciated

thanks

2

Hello @RWilliams,
I’m a little confused. What do you mean by

What im trying to achive is the following with enegy usage for each time window ie 00 - 07 tariff 1 and 07 - 00 tariff 2
I’m not sure what tariff or those numbers refer to.

Maybe you could annotate some of your screen shots?

Are you querying for two sets of data and then wanting to find the difference between them? Like e.g. the you have 7 day windows and you want
(value from today (06/13) - last monday (06/06) )
(the value from sunday (06/12) - last sunday (06/05)
etc for the whole week?

If you’re looking to do that, I’d look into the timeshift function:

Also don’t feel shy to tag me so I don’t miss you.

3

You are going to need to pivot the data into a column for each tariff after your aggregateWindow() function. That means you need to eliminate the filter for hour on your data, and push that into the record processing. You would end up with one record per day with a column for each time range. Someone else will be better than me at the actual flux code though…

1 Like
4

Hi Anaisdg,

I have one data set of data at 30 min intervals. This is the energy used and is cumlitive.

i have two energy tariffs.
1 Between 00:00 and 07:00 this is tariff 1 at is charged at 20p per kwh used
2 Between 07:00 and 00:00 this is tariff 2 at is charged at 27p per kwh used

what i would like to achive is to extract the data into three columes

1 date ( by day)
2 difference in data between 00:01 and 07:00 (first and last values)
3 difference in data between 07:01 and 00:00 (first and last values)

from the three columes make a stacked bar graph that stacks columes 2 and 3 and used the day to group all data.

i hope this makes sence.

thanks