Azure Data Explorer - Kusto Query - Get Data For Last 20 Days

In continuation of my previous post on Get Min/Max from each Category, let’s do one of the most common queries with filter criteria for date time field.

 

For the purpose of simplicity and in order to keep this article more focused, I’ve removed data from all the additional columns as shown below,

 

GenerationDate	DescriptionTitle	DescriptionDetail	FeedKey
2020-10-02 00:00:00:0000000	…	…	…
2020-10-21 00:00:00:0000000	…	…	…
2020-10-21 00:00:00:0000000	…	…	…
2020-10-21 00:00:00:0000000	…	…	…
2020-10-21 00:00:00:0000000	…	…	…
2020-10-22 00:00:00:0000000	…	…	…
2020-10-22 00:00:00:0000000	…	…	…

 

Query description

 

The idea is to fetch all the records, which occurred in the past 20 days of Generation Date.

 

Explanation

 

Now to achieve our expected result, there could be more than one way.

 

Approach 1

 

Find out the date which falls exactly 20 days back using ago(…) and then use conditional operator (<= and >=) to achieve this result.

 

The above approach would work perfectly but the problem with this approach is there are many lines of code and calculation.

 

Approach 2

 

Using the between(...). This approach will have  fewer lines of code.

DemoData
| where  (todatetime(GenerationDate) – now()) between(0d…20d);

NOTE

Make sure to do the proper datetime cast, otherwise  you may end up getting an error ‘Arithmetic expression cannot be carried-out between StringBuffer and DateTime’.

 

Happy Kustoing!