How to use DateTimeBin() function in Azure Cosmos DB

 

     I wrote about the Date_Bucket() function in SQL Server a couple weeks ago. Azure Cosmos DB team announced the same functionality with a different name DateTimeBin() function. It works exactly the same with the Date_Bucket() function of SQL Server.

     Cosmos DB version of the function has the same number of parameters. The order is different. All the datatime parameters must be in ISO 8601 format (YYYY-MM-DDThh:mm:ss.fffffffZ)

DateTimeBin (source, datetimepart, binsize, binatDatetime)

Source

This is the property from your document.

datetimepart

This is the default size of the bin. I wish week and month were on this list too.

datepart	Abbreviations
day	day, dd, d
hour	hour, hh
minute	minute, mi ,n
second	second, ss, s
millisecond	millisecond, ms
microsecond	microsecond, mcs
nanosecond	nanosecond, ns

binsize

This is an optional parameter. You can easily change the default bucket size with the binsize parameter. As I said in datetimepart, the week is not an option of datetimepart. You can easily pick day as  a datetimepart and use 7 for binsize to create weekly bins.

binatDateTime

This is an optional parameter, You can use it to specify a start date. If you don't specify it, the default value will be 1970-1-1

     Here is an example using this function. I have the StackOverflow database in my CosmosDB container. I am using the Cosmos DB Studio in the following example.

I will use the CreatedOn property and find all the posts created by a user weekly in the following example.

It returns 49 items Each item displays the number of posts in a week. Request Charge is not that bad for a function like this.

This function is great if you like to analyze your data quickly, You can use the Analyzer Tab of Cosmos DB Studio to see the whole picture.