Timespan

This is one of the great functions for the lazy coder.

Ever being tired of trying to think your way through a solution to do some easy and dynamic time and date filtering to your code?

Ever had to make several views or stored procedures in SQL to deliver different aggregation levels and filtering?

Here is the KQL answer to all those troubles and hair-pulling coding “nightmares”. The timespan() function.

The timespan() function helps you to an easy approach around filtering and grouping on time and date.

Timespan() gives you the ability to give the power of dynamic filtering and grouping back to the end user, with a very easy adoption around usage and funktionality.

The syntax

The syntax for the timespan() function is quite easy to get stated wtih. It accepts a text input concatenated of two elements - a series of digits and a text-string.

To start with the last one, the text-string, then you can choose from a list of pre-defined text-strings to use:

• ’d’ for days
• ‘h’ for hours
• ’m’ for minutes
• ’s’ for seconds
• ‘ms’ for milliseconds
• ‘micrososecond’ for, yes you’ve quessed it, microsecond
• ’tick’ for nanosecond
• the special ’null’ to return null

Put a self-chosen number of digits in front of the above selected string and you have yourself a fully functioning timespan() function.

The returned result from the function is the number of self-chosen digits and time-element as a dynamic representation of that timespan.

Examples:

// to get the number of hours in a day - 24
print
    HoursInADay = timespan(1d)/timespan(1h)

// or the number of nanoseconds in 3.5 hours (126.000.000.000)
print
    NanosecondsInSevenhours = timespan(3.5h)/timespan(1tick)

The above examples are somewhat simple math questions, and you can of course also use this function for something far more complex and interesting.

Filters

Imagine you have a query and you would like to filter it by the last 100 days - you wish to do that dynamically and normally in SQL you would do something like “where [datetime] > dateadd(‘day’, -100, getdate())”.

Above would look something like this in KQL:

NYCTaxi
| where tpep_pickup_datetime > ago(timespan(100d))

Notice the function ago which helps us with the filtering when using the timespan() function.

Now if you ask your SQL guru to change the query to be dynamic (perhaps in a stored procedure) and the end user should be able to choose the time-element - you might not be the best friend for long.

But in KQL it is quite easy.

If we put the things together from all of the above, we now know how to do that easily with a Kusto Function (TIP: Function is Kusto is the Stored procedure from SQL):

.create function GetTaxiRides(TimeWindow:string) {
NYCTaxi
| where tpep_pickup_datetime > ago(totimespan(TimeWindow))
}

Let me break down the function in pieces:

• First I declare the function name (GetTaxiRides)
• Then I declare the accepted parameters (Timewindow:string)
• Then I build the KQL query with a where clause
• And use a new function totimespan()

The function totimespan() takes a string input and returns the timespan value of that input. This helps us to just write the concatination of the digits and the text-string as mentioned before, without having to know the syntax for a timespan.

Now the end user can call this function and input what ever timespan they need, and get the desired results back. And I think the SQL guru is still coding (I know I would, if I where to create the same functionality in SQL).

Aggregation

The timespan function can also be used to create dynamic aggregation levels in the query.

We can use the same functionality to give the end user even more freedom when asking for data, both the filter and the aggregation levels (and from this the granularity).

Example:

NYCTaxi
| summarize Trips = count() by bin(tpep_dropoff_datetime, totimespan("5d"))

Here I use the timespan() function to aggregate the result set in buckets (or bins) of 5 days.

All of this, can, of course, be put in a Kusto Function for the end user to just put in the values.

Everything put together:

.create function GetTaxiRides(TimeWindow:string, BucketGrouping:string) {
NYCTaxi
| where tpep_pickup_datetime > ago(totimespan(TimeWindow))
| summarize Trips = count() by bin(tpep_dropoff_datetime, totimespan(BucketGrouping))
}

I bet you can imagine the SQL guru trying to accomplish the same functionality from a SQL server. I don’t think I would ever get the same amount of flexibility with my SQL skills.

Use in Power BI also

The Kusto function is nothing without the end user to be able to work with it.

This can be done as example through the Power BI service or the Real-Time Dashboard service. The second one is planned for a future blog post.

When using a Kusto function through the Power BI service (using the Azure Data Explorer connecter, which also connects directly to Fabric), the Power BI service sees the input parameters and exposes it to the Power Query connection and ready to be hard coded.

But the hard coding of parameters is not always usable, especially in this scenario I’m trying to draw out.

You can actually expose the parameters in Power BI - Chris Webb has done some great blogging on the topic right here.

In summary

The timespan() function is just one of the powerfull functions from the KQL engine supporting a lot of services from Microsoft (Fabric Real-Time Intelligence, Azure Data Explorer, Defender, Graph, etc.).

I hope you can see your own usage scenarios from this function and will try to implement it into your solutions.