Hi,
I am looking for an alternative for grouping dataset by month because I read that month and year were not supported in InfluxDB.
Do you have any idea?
Kind regards
Hello @Elisa_Scheer,
Thanks for your question. You have two options, the first is to use the Date processor plugin to add a tag with the month or year to your data and then you can perform a group by on that tag. The second is to use flux to query your data. It allows you to query by year and month.
Hi,
I was eager to try Flux to get month/year grouping but it seems that I did not succeed completely:
- with InfluxDB v1.7+ installed I enabled Flux (flux-enabled = true)
- I ran a script with " |> window(every: …)" and there are results:
" |> window(every: 1y)" = dataset is grouped by year but do not start at 01/01 of a particular year
" |> window(every: 1M)" = returns Error: loc 8:20-8:22: expected an operator between two expressions
What is a REAL solution how to get month/year groups from InfluxDb? Is it Influx2.0?
Hi,
I never managed to got it working, you can check my previous topic Using Flux for retrieving montly data
I was also using the same version of influxdb as yours.
Maybe you can try with 2.0.
I got it working by adding another tag in influxdb so that I was able to group by this tag.
Hello @Radim_Machu,
Can you please share some screenshots of your queries and the outputs? Thank you.
Hi @Elisa_Scheer,
thanks for a link to your previous topic.
At least, now I know that a correct syntax for month interval is ‘1mo’ !
But, unfortunately, as you wrote, it did not give expected results. 
Hello @Anaisdg,
my query is:
from(bucket: “host_db/autogen”)
|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == “fve” and (r._field == “e”) and r.fve == “1” )
|> window(every: 1mo, period: 1mo)
|> spread()
|> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
And the result:
I should state that unfortunately, the result is wrong as expected:
the feature of year/month duration grouping is still under development as can be seen at feat(values): add month support when adding durations to a time value by jsternberg · Pull Request #2067 · influxdata/flux · GitHub
Hi There,
Having similar issues and perhaps this is indeed related to the fact that things are still under development.
When windowing by month, I’m getting a period for Jan that says: _start=2020.01.01T14:25:00Z & _stop=2020.01.23:T00:00Z
Is someone able to shed som light on why Jan 23 is considered the end of the first month here?
Hi Anais, How will it possible if we have already a field column created with date processor plugin and we can’t create both tag and field at once. Anyway we can create a filed and a tag value with date.processor. we have requirement for field key to be get created and for monthly i need to have a tag value as per your solution.
Can you please tell me ,how can do the aggregation for monthly even if I get the the Year and Month value…what should be the group by including year and time. suppose I want to do the aggregation every month 1st …what should be in the group by clause
Hello @oom are you using 1.x?
This is supported in Flux. What version of InfluxDB are you using? feat(values): add month support when adding durations to a time value by jsternberg · Pull Request #2067 · influxdata/flux · GitHub
Yes the date processor adds a tag or a field with the month.
telegraf/date.go at da5991d16c104c811184cfb7b29b10b2d08b580e · influxdata/telegraf · GitHub
Hi , We are still with 1.8 Enterprise. I do created the date and month but how can it do the monthly aggregates. Is it something if,
1-> I have to do group by time(31d),month, year will work…(does 30d takes from beginning of month)?
2-> shall i do group by month,year and if so how can it identify or on each month end calculate the aggregates.
We need that CQ to run every month end to generate the report
Hello @oom,
Why don’t you want to write the months as a tag? Then you could group by month. Grouping by 31d won’t always work since not every month is 31 days long.
Hi Anaisdg,
Agreed and used the month and year as tag. The problem is that how can I make it automated otherwise I have to do manually. Anyway we can run it every month end so that it load into a new measurement.
Like CQ , it automatically runs in every interval, same functionality can it be achieved. we have to store monthly aggregates in diff measurement
Hello @oom,
Ahhh I see. Yah I’m not sure that 1.x has a good workaround for this.
However you can group by month with Flux and create tasks that run on a monthly basis. Flux is “month aware” so if you run a task with the every parameter set to 1m and you started your task today, then your task would run April 20, May 20, etc. You can also include an offset to start the task on the first or last day of every month.
