Query for current month in flux

Welcome & Getting Started
1

Hey i have seen a lot of open issues regarding missing time query functions for current/last month/year and so on in influxdb.

Now using flux there are date functions

I thought i could use them to get only results for the current month with this query:

import "date"

month = date.monthDay(t: now())
from(bucket: "piMeter") 
|> range(start: month*(-1))
|> filter(fn: (r) => r._measurement == "downsampled_energy" and r._field == "sum_Gesamt") 
|> fill(value: 0.0) 
|> aggregateWindow(every: 1d, fn:sum)

but this gives me 18k results starting in 1970. Looks like the range gets not processed. Setting month variable manually to say -3 ends up with the same result.

Am i missing something here?

1 Like
2

@digitaldex date.monthDay returns an integer representing the current month (1-12). range accepts either an RFC3339 formatted timestamp or an integer. If you provided an integer, it parses it as a nanosecond epoch timestamp. Your current range function is getting parsed as (assuming today’s date of January 2, 2020):

range(start: 1*-1)
// OR
range(start: -1)

Which is 1 nanosecond before the epoch start date/time, January 1, 1970.

Currently, Flux doesn’t support time values in mathematical operations, but you have a few options. For this specific use case, use the date.truncate function to truncate today’s timestamp to the month, which will return a timestamp representing the first day of the current month:

import "date"

month = date.truncate(t: now(), unit: 1mo)

from(bucket: "piMeter") 
|> range(start: month)
|> filter(fn: (r) => r._measurement == "downsampled_energy" and r._field == "sum_Gesamt") 
|> fill(value: 0.0) 
|> aggregateWindow(every: 1d, fn:sum)
1 Like
3

Thanks for the detailed answer. I have used your query but was wondering why i get 8 result records.

import "date" 
month = date.truncate(t: now(), unit: 1mo)
from(bucket: "piMeter") 
|> range(start: month)
|> filter(fn: (r) => r._measurement == "downsampled_energy" and r._field == "sum_Gesamt")
|> fill(value: 0.0) 
|> aggregateWindow(every: 1d, fn:sum)

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,string,string,double,dateTime:RFC3339
#group,false,false,true,true,true,true,false,false
#default,_result,
,result,table,_start,_stop,_field,_measurement,_value,_time
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,14140.849999999982,2019-12-27T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,9142.019999999997,2019-12-28T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,14687.989999999987,2019-12-29T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,12170.26000000001,2019-12-30T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,10926.41,2019-12-31T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,14273.199999999995,2020-01-01T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,12017.090000000031,2020-01-02T00:00:00Z
,0,2019-12-26T00:00:00Z,2020-01-02T17:04:02.959676018Z,sum_Gesamt,downsampled_energy,6046.569999999999,2020-01-02T17:04:02.959676018Z

Looks like this is not working as intended

4

@digitaldex Oh, I just realized you’re probably using a version of Flux that doesn’t support the mo duration unit yet. It was introduced in Flux 0.53, but I’m guessing you’re using InfluxDB 1.7. The most recent version of Flux packaged with InfluxDB 1.7 is Flux 0.50.

It’s not as graceful, but you could manually build the month timestamp as a string, then convert it to a time. The following example uses string interpolation and the strings.substring function to extract the year and month from the timestamp returned from now():

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

from(bucket: "piMeter") 
  |> range(start: month)
  |> filter(fn: (r) => r._measurement == "downsampled_energy" and r._field == "sum_Gesamt")
  |> fill(value: 0.0) 
  |> aggregateWindow(every: 1d, fn:sum)
5

This is working like a charm. Thanks again!

6

No problem. Happy to help!

7

Cześć, temat jest już stary ale może ktoś mi pomoże. Jak wyciągnąć dane z poprzedniego miesiąca oraz jak z poprzedniego tygodnia? Czy są już możliwe funkcje matematyczne na datach?
Pozdrawiam
Lukas