Azure Data Explorer - Kusto Query - Get data for last 20 days

In continuation to my previous post on 'Get Min/Max from each category', this time let’s do one of the most demanded queries with filter criteria on date time field.

For the purpose of simplicity and keep this article more focused, I have 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 past 20 days of GenerationDate.

Approaches

Now to achieve our expected result, there could be more than a way.  One way could be:

Approach 1

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

Above approach would work perfectly but the problem with this approach is lot many lines of code and calculation.

Approach 2

The other approach having very less lines of code could be using the between(…) as shown below:

DemoData   

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

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

Happy kustoing!