Kql Between Times: What It Means & How to Use It

Kql between times is a phrase that appears when someone is looking for the Kusto Query Language (KQL) syntax to filter events that fall between two specific times. The correct KQL operator is between, and you use it in a where clause with a datetime range, like where Timestamp between (datetime(2024-01-01) .. datetime(2024-01-02)). This article explains the exact syntax, common variations, and pitfalls when working with time ranges in KQL.

Formula

The between operator in KQL is used inside a where clause to filter rows where a datetime column falls within a range. The general form is:

Table | where Column between (startDatetime .. endDatetime)

Where:

• Column – the datetime column you are filtering (e.g., Timestamp, TimeGenerated).
• startDatetime – the lower bound, inclusive.
• endDatetime – the upper bound, inclusive.

The range is inclusive on both ends. If you need exclusive bounds, use > and < instead. The syntax is case-insensitive for keywords, but column names are case-sensitive.

Worked Example

Suppose you have a table named Events with a column Timestamp of type datetime. You want events from January 1, 2024, 00:00:00 to January 2, 2024, 00:00:00 (inclusive). The query is:

Events
| where Timestamp between (datetime(2024-01-01 00:00:00) .. datetime(2024-01-02 00:00:00))

This returns all events where Timestamp is exactly 2024-01-01 00:00:00 or later, and exactly 2024-01-02 00:00:00 or earlier. The range is inclusive, so the end time is included. If you wanted to exclude the exact end minute, you would use Timestamp < datetime(2024-01-02 00:00:00) instead.

For the calculator above, if you enter start time 2024-01-01 00:00:00 and end time 2024-01-02 00:00:00, the difference is 24 hours, 1 day, and 1440 minutes. The calculator simply subtracts the two datetimes, which is the same operation you would do in KQL to compute a duration.

Understanding KQL Time Types

KQL uses the datetime type for timestamps. When you write a literal, you must use datetime() with a string in ISO 8601 format, e.g., datetime(2024-03-15T10:30:00Z). You can also use now() for the current time, and ago() for a relative time, like ago(1h) for one hour ago.

Time is always stored in UTC in KQL, unless your cluster is configured otherwise. When you display times, they may be converted to local time by the client, but the underlying data is UTC. This is a common source of off-by-one-hour errors when you are filtering by local time ranges.

Common Mistakes and How to Avoid Them

• Using between outside a where clause – between is not a standalone operator; it must be used as part of a comparison in where.
• Forgetting the double dot – the range separator is two dots .., not a single dot or a comma.
• Using string comparison – if you filter with Timestamp > "2024-01-01", KQL may implicitly convert the string to datetime, but it is safer to use datetime().
• Inclusive vs exclusive – between includes both endpoints. If you want to exclude the end, use <.
• Timezone confusion – always use UTC in your queries unless you intentionally want local time. Use datetime("2024-01-01T00:00:00Z") for UTC.

Alternatives to between for Time Filtering

Sometimes you need more complex time filters. Here are alternatives:

• where Timestamp >= datetime(2024-01-01) and Timestamp < datetime(2024-01-02) – exclusive end, common for daily buckets.
• where Timestamp between (now(-1h) .. now()) – last hour, using now() with a negative offset.
• where Timestamp between (startofday(now()) .. now()) – from midnight today to now.
• where Timestamp between (datetime(2024-01-01) .. endofday(datetime(2024-01-01))) – all of Jan 1, 2024, including the last second.

Performance Considerations

When filtering on time, KQL can use the time column as a partition key if the table is partitioned by time (which is common in Azure Data Explorer and Log Analytics). To get the best performance, always put the time filter as early as possible in the query, ideally before other filters. Also, avoid wrapping the datetime column in functions like startofday() in the filter, because that prevents the engine from using the index. Instead, compute the range boundaries first.

Frequently Asked Questions

How do I filter between two specific dates in KQL?

Use where Timestamp between (datetime(2024-01-01) .. datetime(2024-01-02)). This includes both dates. If you want to include all of Jan 1, use between (datetime(2024-01-01) .. datetime(2024-01-01 23:59:59)) or use startofday and endofday.

Does between include the end time?

Yes, between is inclusive of both start and end. If you want to exclude the end, use >= and <.

Can I use between with ago()?

Yes, you can use between (ago(1d) .. now()) to get the last 24 hours. The ago() function returns a datetime relative to the current time.

Why does my time filter return no results?

Check the timezone. If your data is in UTC and you are entering local times, you may be filtering the wrong range. Convert your local times to UTC, or use datetime("2024-01-01T00:00:00Z") to specify UTC.

How do I filter by time of day, like between 9 AM and 5 PM?

Use where hour(Timestamp) between (9 .. 17) or where Timestamp between (datetime(2024-01-01 09:00) .. datetime(2024-01-01 17:00)) for a specific date. For every day, use hour().

Related

• HEVC Bitrate Calculator: How to Use It
• Bitrate to Bandwidth Calculator: How to Convert
• Bitrate Sensitivity Calculator: How It Works

in t | Start time | datetime | 2024-01-01 00:00:00 | 0
in t2 | End time | datetime | 2024-01-02 00:00:00 | 0
out hours | Hours between | h | (t2 - t) / 1h | 2
out days | Days between | d | (t2 - t) / 1d | 2
out minutes | Minutes between | min | (t2 - t) / 1m | 0