Axiom
Overview

datetime_add

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, status

Run in Playground

Output

_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'], duration

Run in Playground

Output

_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, method

Run in Playground

Output

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.

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

Other query languages#

Splunk SPL users

In Splunk SPL, you typically use relative_time(_time, "+1mon") to shift a timestamp by a calendar unit. In APL, the datetime_add function takes the date part as a string, the number of units, and the base datetime as separate arguments.

Splunk example

... | eval new_time=relative_time(_time, "+1mon")

APL equivalent

... | extend new_time = datetime_add('month', 1, _time)
ANSI SQL users

In ANSI SQL, you typically use DATEADD(month, 1, timestamp_column) or equivalent interval arithmetic to shift a timestamp. In APL, datetime_add uses the same conceptual pattern with a string-based part name.

SQL example

SELECT DATEADD(month, 1, timestamp_column) AS new_time FROM events;

APL equivalent

['dataset']
| extend new_time = datetime_add('month', 1, _time)

Updated

Was this page helpful?