How to loop over data in KQL based on a customized timestamp?

October 12, 2023

I have the following KQL query:

let StartTime = todatetime("2023-10-11");
let EndTime = todatetime("2023-10-12");
Telemetry
| where Timestamp between (StartTime .. EndTime)
| where Name == "Scale"
| project Timestamp, Name, Value

Is there a way to project the data from the 11th of October to the 12th only from 03:00:00 to 15:00:00. So instead of showing values everyday from 00:00:00 to 23:59:59 I only the need the data of every single day from 03:00:00 to 15:00:00 as if the StartTime of each day would be 03:00:00 and EndTime 15:00:00 ??

Expected example:

enter image description here

I don’t know if this is possible in kusto, I tried several approaches by binning the timestamp but it didn’t work.

Can someone please help ?

>Solution :

Sure it’s possible, KQL is awesome 🙂

let StartTime = todatetime("2023-10-11");
let EndTime = todatetime("2023-10-12");
Telemetry
| where Timestamp between (StartTime .. EndTime)
| where hourofday(Timestamp) between (3 .. 14) //0300 to 1459
| where Name == "Scale"
| project Timestamp, Name, Value