Axiom
Overview

startofmonth

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 asc

Run in Playground

Output

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 asc

Run in Playground

Output

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 asc

Run in Playground

Output

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.

  • 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.

Other query languages#

Splunk SPL users

In Splunk SPL, you use relative_time with the @mon snap-to modifier to round a timestamp to the start of the month. In APL, the startofmonth function achieves the same result and supports an optional month offset.

Splunk example

... | eval month_start=relative_time(_time, "@mon")

APL equivalent

... | extend month_start = startofmonth(_time)
ANSI SQL users

In ANSI SQL, you use DATE_TRUNC('month', timestamp_column) to truncate a timestamp to the first day of the month. In APL, startofmonth provides the same functionality with an optional offset parameter.

SQL example

SELECT DATE_TRUNC('month', timestamp_column) AS month_start FROM events;

APL equivalent

['dataset']
| extend month_start = startofmonth(_time)

Updated

Was this page helpful?