startofmonth
This page explains how to use the startofmonth function in APL.
Use the startofmonth function in APL to round a datetime value down to the first day of the month at midnight (00:00:00). You can optionally shift the result by a specified number of months using the offset parameter.
You can use startofmonth to bin events into monthly buckets for aggregation, reporting, and trend analysis. This is especially useful for monthly summaries, billing cycle calculations, and long-term trend monitoring.
Use it when you want to:
- Aggregate events or metrics by month.
- Align timestamps to month boundaries for consistent reporting.
- Track month-over-month changes in activity or error rates.
Usage
Syntax
startofmonth(datetime [, offset])Parameters
| Name | Type | Description |
|---|---|---|
| datetime | datetime | The input datetime value. |
| offset | long | Optional: The number of months to offset from the input datetime. Default is 0. |
Returns
A datetime representing the start of the month (first day at 00:00:00) for the given date value, shifted by the offset if specified.
Use case examples
Count requests per month to identify monthly traffic trends.
Query
['sample-http-logs']
| extend month_start = startofmonth(_time)
| summarize request_count = count() by month_start
| sort by month_start ascOutput
| month_start | request_count |
|---|---|
| 2024-11-01T00:00:00Z | 42350 |
| 2024-12-01T00:00:00Z | 45120 |
| 2025-01-01T00:00:00Z | 38900 |
This query bins each HTTP request to the start of its month and counts the total requests per month.
Track the monthly average span duration for each service.
Query
['otel-demo-traces']
| extend month_start = startofmonth(_time)
| summarize avg_duration = avg(duration) by month_start, ['service.name']
| sort by month_start ascOutput
| month_start | service.name | avg_duration |
|---|---|---|
| 2024-11-01T00:00:00Z | frontend | 00:00:01.2340000 |
| 2024-12-01T00:00:00Z | frontend | 00:00:01.1750000 |
| 2025-01-01T00:00:00Z | frontend | 00:00:01.2890000 |
This query groups trace spans by month and service, then calculates the average span duration for each combination.
Monitor monthly server error volume to spot months with elevated failure rates.
Query
['sample-http-logs']
| where toint(status) >= 500
| extend month_start = startofmonth(_time)
| summarize error_count = count() by month_start
| sort by month_start ascOutput
| month_start | error_count |
|---|---|
| 2024-11-01T00:00:00Z | 156 |
| 2024-12-01T00:00:00Z | 203 |
| 2025-01-01T00:00:00Z | 134 |
This query filters for server errors and counts them per month to reveal monthly error patterns.
List of related functions
- endofmonth: Returns the end of the month for a datetime value.
- startofday: Returns the start of the day for a datetime value.
- startofweek: Returns the start of the week for a datetime value.
- startofyear: Returns the start of the year for a datetime value.
- monthofyear: Returns the month number from a datetime value.