endofmonth

This page explains how to use the endofmonth function in APL.

Use the endofmonth function in APL to calculate the end of the month for a given datetime value. The function returns a datetime set to the last moment of the final day of the month (23:59:59.9999999), with an optional offset to shift forward or backward by a specified number of months.

You can use endofmonth to create monthly time boundaries for aggregation, billing cycles, and reporting. This is especially useful when you need to bucket events into monthly intervals or define month-end deadlines.

Use it when you want to:

• Define end-of-month boundaries for monthly reports and dashboards.
• Aggregate events to monthly intervals for billing or usage analysis.
• Build monthly summaries across log, trace, or security datasets.

For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.

Splunk SPL users

In Splunk SPL, there is no direct equivalent to endofmonth. You typically use manual date math with eval and relative_time to calculate the last day of the month. In APL, the endofmonth function handles this directly and supports an optional month offset.

Splunk example

... | eval month_end=relative_time(now(), "@mon+1mon-1d@d+86399")

... | extend month_end = endofmonth(_time)

ANSI SQL users

In ANSI SQL, you often use LAST_DAY(timestamp) or combine DATE_TRUNC with interval arithmetic to get the end of the month. In APL, the endofmonth function provides this directly and supports an optional month offset.

SQL example

SELECT DATE_TRUNC('month', timestamp_column) + INTERVAL '1 month' - INTERVAL '1 second' AS month_end FROM events;

['dataset']
| extend month_end = endofmonth(_time)

Usage

Syntax

endofmonth(datetime [, offset])

Parameters

Name	Type	Description
datetime	datetime	The input datetime value.
offset	long	Optional: The number of months to offset from the input date. Default is 0.

Returns

A datetime representing the last moment of the month for the given date, shifted by the offset if specified.

Use case examples

Log analysis

Count requests by month boundary to track monthly traffic volume.

Query

['sample-http-logs']
| extend month_end = endofmonth(_time)
| summarize total_requests = count() by month_end
| sort by month_end asc

Run in Playground

Output

month_end	total_requests
2024-10-31T23:59:59.9999999Z	18432
2024-11-30T23:59:59.9999999Z	19871
2024-12-31T23:59:59.9999999Z	17654

This query groups HTTP log events by end-of-month boundaries and counts the total requests in each month.

OpenTelemetry traces

Track monthly average trace durations for each service to identify long-term performance trends.

Query

['otel-demo-traces']
| extend month_end = endofmonth(_time)
| summarize avg_duration = avg(duration) by month_end, ['service.name']
| sort by month_end asc

Run in Playground

Output

month_end	service.name	avg_duration
2024-10-31T23:59:59.9999999Z	frontend	00:00:01.2150000
2024-11-30T23:59:59.9999999Z	frontend	00:00:01.2780000
2024-12-31T23:59:59.9999999Z	frontend	00:00:01.1930000

This query shows how average span duration changes month by month for each service, helping you spot long-term performance shifts.

Security logs

Identify monthly error spikes to detect months with elevated server failure rates.

Query

['sample-http-logs']
| where toint(status) >= 500
| extend month_end = endofmonth(_time)
| summarize error_count = count() by month_end
| sort by month_end asc

Run in Playground

Output

month_end	error_count
2024-10-31T23:59:59.9999999Z	187
2024-11-30T23:59:59.9999999Z	234
2024-12-31T23:59:59.9999999Z	162

This query counts server errors by month to help you identify months with unusually high failure rates.

List of related functions

• startofmonth: Returns the start of the month for a datetime, useful for defining the beginning of monthly intervals.
• endofday: Returns the end of the day for a datetime.
• endofweek: Returns the end of the week for a datetime.
• endofyear: Returns the end of the year for a datetime.
• monthofyear: Returns the month number from a datetime, useful for month-based grouping.