How to get the start and end of an hour from a datetime in KQL?

Question

In ADX (Kusto), given a datetime value, how can I get the start and end of the hour?

Input: datetime(2023-07-21 12:11:10)

Expected output:

start: datetime(2023-07-21 12:00:00)
end: datetime(2023-07-21 12:59:59)

Answer 1

Using make_datetime, you can define the following functions:

.create-or-alter function with (docstring = "Start of hour") startofhour(dt:datetime) {
    make_datetime(datetime_part('Year', dt), datetime_part('Month', dt), datetime_part('Day', dt), datetime_part('Hour', dt), 0, 0)
}  

.create-or-alter function with (docstring = "End of hour") endofhour(dt:datetime) {
    make_datetime(datetime_part('Year', dt), datetime_part('Month', dt), datetime_part('Day', dt), datetime_part('Hour', dt), 59, 59.9999999)
}

which is executable like so:

print startofhour(datetime(2023-07-21 12:11:10)), endofhour(datetime(2023-07-21 12:11:10))

and the result is:

print_0	print_1
2023-07-21T12:00:00Z	2023-07-21T12:59:59Z