In my Kusto/ADX Timeseries Database i have a custom timestamp field because the ingestion process takes quite a bit and i need the exact time for my entries.
Now i want to query results with a certain resolution (i.e. 1 Minute Averages over 15 Minutes), so i would use range.
But range uses the insert timestamp if i understand it correctly.
Can i make range use my custom timestamp (of type datetime) field?
If I understand your question correctly, you want to return the average of a certain value in 1-minute timeframes. Assuming your table is structured like this, you can use the below query to get the average value per 1-minute timeframe.
| Column name | Data type | Description |
|---|---|---|
| MyCustomTimestamp | Timestamp | Your custom timestamp value |
| Value | Integer | The value that you want the average of in 1-minute timeframes |
Table structure for TableName
TableName
// Query only the last 15 minutes of data
| where MyCustomTimestamp > ago(15m)
// Using the bin() function to round the timestamp down to 1 minute
| summarize Average = avg(Value) by Timeframe = bin(MyCustomTimestamp, 1m)
The query broken down into steps:
MyCustomTimestamp is greater than 'now - 15 minutes'Timeframe which is equal to the MyCustomTimestamp value rounded down to the nearest full minute. I.e. 12:04:33 is rounded down to 12:04:00Value for every column that falls within the same 1 minute (summarize by Timeframe).