datetime_add
This page explains how to use the datetime_add function in APL.
Use the datetime_add function in APL to calculate a new datetime by adding a specified number of date parts to a base datetime value. You can add years, months, weeks, days, hours, minutes, seconds, or smaller units. Use negative values to subtract.
You can use datetime_add to shift timestamps forward or backward for time-window comparisons, expiration calculations, and timezone adjustments.
Use it when you want to:
- Project future or past timestamps relative to an event.
- Define time ranges around a known incident or deadline.
- Shift trace or log timestamps for timezone normalization.
Usage
Syntax
datetime_add(part, value, datetime)Parameters
| Name | Type | Description |
|---|---|---|
| part | string | The unit of time to add: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond'. |
| value | int | The number of units to add. Use a negative value to subtract. |
| datetime | datetime | The base datetime value. |
Returns
A datetime value after adding the specified interval to the base datetime.
Use case examples
Project what the time is 1 hour after each request to estimate cache expiration windows.
Query
['sample-http-logs']
| extend future_time = datetime_add('hour', 1, _time)
| project _time, future_time, method, statusOutput
| _time | future_time | method | status |
|---|---|---|---|
| 2025-01-15T10:00:00Z | 2025-01-15T11:00:00Z | GET | 200 |
| 2025-01-15T10:05:00Z | 2025-01-15T11:05:00Z | POST | 201 |
| 2025-01-15T10:12:00Z | 2025-01-15T11:12:00Z | GET | 404 |
This query adds 1 hour to each request timestamp, which is useful for estimating when cached responses expire.
Shift trace timestamps forward by 30 minutes to simulate a timezone adjustment.
Query
['otel-demo-traces']
| extend adjusted_time = datetime_add('minute', 30, _time)
| project _time, adjusted_time, ['service.name'], durationOutput
| _time | adjusted_time | ['service.name'] | duration |
|---|---|---|---|
| 2025-01-15T08:00:00Z | 2025-01-15T08:30:00Z | frontend | 00:00:01.2340000 |
| 2025-01-15T08:01:00Z | 2025-01-15T08:31:00Z | cart | 00:00:00.5670000 |
| 2025-01-15T08:02:00Z | 2025-01-15T08:32:00Z | checkout | 00:00:02.1000000 |
This query shifts each trace timestamp forward by 30 minutes, useful for aligning traces from systems that report in different time offsets.
Find requests that occurred within 1 day before a known incident time.
Query
['sample-http-logs']
| where _time between (datetime_add('day', -1, datetime(2025-01-15)) .. datetime(2025-01-15))
| summarize count() by status, methodOutput
| status | method | count_ |
|---|---|---|
| 200 | GET | 1204 |
| 500 | POST | 37 |
| 404 | GET | 89 |
This query uses datetime_add to define a 1-day window before a known incident, helping you investigate the activity that preceded it.
List of related functions
- datetime_diff: Calculates the difference between two datetime values. Use when you need to measure elapsed time rather than shift a timestamp.
- ago: Subtracts a timespan from the current UTC time. Use for simple relative time filters based on
now(). - now: Returns the current UTC time.
- startofmonth: Returns the start of the month for a datetime, useful for month-boundary calculations.
- endofmonth: Returns the end of the month for a datetime.