OData v4 groupby with dates

Question

I have an OData query hitting a WebAPI 2 OData Controller. I want to group records using a DateTime field. I have the following query:

$apply=groupby((day(DateTime),month(DateTime),year(DateTime)),aggregate(Id with countdistinct as Count))

This errors with the following response:

{
  "error":{
    "code":"",
    "message":"The query specified in the URI is not valid. Expression expected at position 22 in 'groupby((day(DateTime)),aggregate(Id with countdistinct as Count))'."
  }
}

The DateTime field is a C# DateTime in the Code-First model I used to generated the DB.

I assume this is because the DateTime functions can only be used in filters?

Is there a way of achieving what I'm aiming for here in OData using the Data Aggregation Extensions supported in WebAPI 2?

Answer 1

It's not supported yet, open an issue for you to track: https://github.com/OData/WebApi/issues/793

contribution is warmly welcome.

Answer 2

I struggled with this too and found the following syntax works to group OData by month and year. You could extend this if you need it by day.

.../_odata/v2.0/WorkItems?$apply=compute(year(CreatedDate) as y, month(CreatedDate) as m, day(CreatedDate) as d)/groupby((y,m,d),aggregate(Id with countdistinct as Count))

I think you might still have a problem with that count distinct. I don't believe it is supported by aggregation. I got this error when I tried it.

Queries which apply a count distinct with an aggregation are not supported by Analytics.

However, removing the aggregation, or doing a sum type aggregation worked fine for me, and since your question was about date aggregation hopefully this points you in the right direction.