Tuesday, October 20, 2020

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!

    No comments:

    Post a Comment