Is there a way to accurately filter using the user-selected relative time range within a KQL query?

Question

I am using Azure Managed Grafana v9.5.16 and ADX/KQL. I am running into obstacles when users filter via relative dates (ie 'last 7 days'). In contrast, all is working as expected when using Absolute time range (selecting From, To, and then Apply time range). There appears to be an issue that, when the relative time picker is used, the start date is set incorrectly and passed in through the user's query. Are there any known fixes or workarounds (that work for non-datetime outputs)

When I use | where $__timeFilter(CreatedDateTime) in the KQL tab (not in Builder), the generated query is incorrect. It tends to be just a couple of days long, no matter the relative timespan selected.

For example, when selecting 'Last 7 days'

my KQL query contains

TestTable
| where $__timeFilter(CreatedDateTime)

and Query Inspector shows

TestTable | where CreatedDateTime >= datetime(2024-02-27T19:02:18.788Z) and CreatedDateTime <= datetime(2024-02-28T19:02:18.789Z)

The following visualization is the output

When I select dates via absolute time range for a similar date range, this is the output

The generated query from

TestTable
| where CreatedDateTime >= datetime(2024-02-21T08:00:00Z) and CreatedDateTime <= datetime(2024-02-29T07:59:59Z)

Answer 1

Did you try using $__timeFrom() and $__timeTo() macros in your KQL queries instead of using the $__timeFilter macro,

TestTable | where CreatedDateTime >= $__timeFrom() and CreatedDateTime <= $__timeTo()

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **