'How to make make_series group by top of the hour and not on the exact time when query is run

The following query gets me sales per hour with 0 for empty hours when no sales exists

tilldevicedata
| where todatetime(TransactionTimeStampUtc) between (_startTime.._endTime)
| where Brand has_any (_brand)
| where Country == _country
| where Store has_any (_store)
| make-series sum(TransactionValueGross) on todatetime(TransactionTimeStampUtc) from _startTime to _endTime step 1h

The problem is that the resulting time stamps are the exact time the query runs like below

"TransactionTimeStampUtc": [
    "2022-05-13T07:40:22.0000000Z",
    "2022-05-13T08:40:22.0000000Z",
    "2022-05-13T09:40:22.0000000Z",
    "2022-05-13T10:40:22.0000000Z",
    "2022-05-13T11:40:22.0000000Z",
    "2022-05-13T12:40:22.0000000Z",
    "2022-05-13T13:40:22.0000000Z",
    "2022-05-13T14:40:22.0000000Z",
    "2022-05-13T15:40:22.0000000Z",
    "2022-05-13T16:40:22.0000000Z",
    "2022-05-13T17:40:22.0000000Z",
    "2022-05-13T18:40:22.0000000Z",
    "2022-05-13T19:40:22.0000000Z",
    "2022-05-13T20:40:22.0000000Z",
    "2022-05-13T21:40:22.0000000Z",
    "2022-05-13T22:40:22.0000000Z",
    "2022-05-13T23:40:22.0000000Z",
    "2022-05-14T00:40:22.0000000Z",
    "2022-05-14T01:40:22.0000000Z",
    "2022-05-14T02:40:22.0000000Z",
    "2022-05-14T03:40:22.0000000Z",
    "2022-05-14T04:40:22.0000000Z",
    "2022-05-14T05:40:22.0000000Z",
    "2022-05-14T06:40:22.0000000Z"
],

I would like it to be on top of the hour so to say at 07:00:00 and 08:00:00 so that everything that falls within that hours ends up on the top of the hour timestamp because the resulting column graph looks off when the values are not exactly on the hour.

Can this be achieved?

column graph

as you can see in the picture I ran the query at hh:07 so its shifted slightly to the right of the hours, and the later I run the query the more they will shift to the right of the top of the hour shown in the graph, I want them to always show exactly at hh:00:00 so they look centered on the hour they occured in

azure-data-explorerkql


Solution 1:[1]

If you are not using from, the step is aligned to a round hour.
If you are using from, the step is aligned to the from value.

from bin(_startTime,1h) to _endTime

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions