Using KQL (Kusto query language), how to group datetimes into weeks (or 7-day chunks)?-CodePudding

I am running KQL (Kusto query language) queries against Azure Application Insights. I have certain measurements that I want to aggregate weekly. I am trying to figure out how to split my data into weeks.

To illustrate what I seek, here is a query that computes daily averages of the duration column.

requests 
| where timestamp > ago(7d)
| summarize 
    avg(duration)
    by 
        Date = format_datetime(timestamp, "yyyy-MM-dd")

This produces something similar to this:

In the above I have converted datetimes to string and thus effectively "rounded them down" to the precision of one day. This may be ugly, but it's the easiest way I could think of in order to group all results from a given day. It would be trivial to round down to months or years with the same technique.

But what if I want to group datetimes by week? Is there a nice way to do that?

I do not care whether my "weeks" start on Monday or Sunday or January 1st or whatever. I just want to group a collection of KQL datetimes into 7-day chunks. How can I do that?

Thanks in advance!

CodePudding user response：

Looks like you are looking for the "bin()" function:

requests 
| where timestamp > ago(7d)
| summarize 
    avg(duration)
    by 
        bin(timestamp, 1d) // one day, for 7 days change it to 7d

CodePudding user response：

I found out that I can use the week_of_year function to split datetimes by week number:

requests 
| where timestamp > ago(30d)
| summarize 
    avg(duration)
    by 
        Week = week_of_year(timestamp)
| sort by Week