How to use variables for the range function based to the month

Fluxlang
1

The aim is to output data for a defined month. For this I use variables for year and month. The problem is that the months have different numbers of days. This means that this code only works for months with 31 days. There is an error message for months with less than 31 days. Now I thought I would also set a variable in “month_off = date.truncate” for the day “31”. E.g. the result of the calculation “date.monthDay”. Unfortunately, I can’t figure out the syntax here.

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Berlin")
monthDay = date.monthDay(t: ${jahr}-${monat}-01T00:00:00.00000000Z)
month_on = date.truncate(t: ${jahr}-${monat}-01T00:00:00Z, unit: 1mo)
month_off = date.truncate(t: ${jahr}-${monat}-31T23:59:59Z, unit: 1mo)

from(bucket: "telegraf")
  |> range(start: month_on, stop: month_off)
  |> filter(fn: (r) => r["_measurement"] == "modbus" and r["_field"] == "Netzbezug_Energie")
  |> aggregateWindow(every: 1d, timeSrc: "_start", fn: spread, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {"Netzbezug_Energie": "Netzbezug"})
  |> drop(columns: ["_start", "_stop", "_measurement", "host", "name", "slave_id", "type"])  
  |> yield(name: "spread")

Now I wanted to change the “month_off” line to:

month_off = date.truncate(t: ${jahr}-${monat}-{monthDay}T23:59:59Z, unit: 1mo)

Unfortunately the syntax doesn’t work.
Does anyone have an idea or solution?

2

@Cavekeeper

I am not very versed in variables, but maybe this will help?

1 Like
3

Ok, I was able to create the solution myself.
This is what the working code looks like.
The solution is in lines 4-6

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Berlin")
option now = () => ${jahr}-${monat}-01T00:00:00Z
month_on = date.truncate(t: 0mo, unit: 1mo)
month_off = date.truncate(t: 1mo, unit: 1mo)

from(bucket: "telegraf")
  |> range(start: month_on, stop: month_off)
  |> filter(fn: (r) => r["_measurement"] == "modbus" and r["_field"] == "Netzbezug_Energie")
  |> aggregateWindow(every: 1d, timeSrc: "_start", fn: spread, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {"Netzbezug_Energie": "Netzbezug"})
  |> drop(columns: ["_start", "_stop", "_measurement", "host", "name", "slave_id", "type"])  
  |> yield(name: "spread")
2 Likes