here is a breakdown of my code
the first lines allow me to extract the current month. the command now() will return somethign formated as YYYY-MM-DDTHH:MM:SSZ
so i am using substring to extract the first 8 chars from this return which amount to YYYY-MM- which i then concatenate with 01T00:00:00Z so that i get the string of the current year/month starting at the first day of that month at midnight.
import "strings"
month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")
next i select which database i am using and filter it. in this case, i am pulling from my database home_energy using the month value i previously calculated before and the current date/time and the start and end. i am choosing the two values returned from the IoTaWatt Mains1_W and Mains2_W which are the watts variable for my two mains
from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
the next lines are needed to perform the calculations to add the two separate values and to multiply them by the total cost i pay per KWh. the first row makes a pivot table to combine the two values in the table so they are marched by time and creates a intermediary variable “_value”
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: (r.Mains1_W + r.Mains2_W) * 0.14552 }))
the next lines perform the windowing and the integral to get watt-hours from the watts the IoTaWatt returns. if you see the second line “every: 30d,” this is telling grafana to display 30 days worth of information. the lines “|> integral(unit: 1h) |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))” are used to calculate watt hours and then dividing by 1000 gives me kilo-watt hours
|> aggregateWindow(
every: 30d,
fn: (tables=<-, column) =>
tables
|> integral(unit: 1h)
|> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
the last lines are “cleanup” lines where i am making things more presentable. right now if i was to look at all of the RAW data InfluxDB has sent, i would have the Mains1_W, mains2_W, the total watts i calculated, the integral, and many other columns of data which i no longer care about since i have used them to generate my final value. the “keep” command tells grafana which columns i want to keep, and so i drops all of the other data. the final one renames the column to what i want. in this case i rename it to describe the particular breakers are being monitored etc.
|> keep(columns: ["_value", “_time”])
|> rename(columns: {_value: “Total Energy Uage Per Day”, _time: “Date”})