Axiom
Overview

datetime_diff

You can use datetime_diff to measure elapsed time between events, calculate how long ago something occurred, or compare timestamps across records.

Use it when you want to:

  • Calculate the number of hours, days, or minutes between two events.
  • Measure how long ago a request or trace occurred relative to the current time.
  • Compare event timestamps to detect delays or gaps in processing.

Usage#

Syntax#

datetime_diff(part, datetime1, datetime2)

Parameters#

Name Type Description
part string The unit for the result: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond', 'nanosecond'.
datetime1 datetime The later datetime value (left side of subtraction).
datetime2 datetime The earlier datetime value (right side of subtraction).

Returns#

A long representing the number of periods of the specified unit in the result of datetime1 - datetime2.

Use case examples#

Calculate how many hours ago each request occurred.

Query

['sample-http-logs']
| extend hours_ago = datetime_diff('hour', now(), _time)
| project _time, hours_ago, method, status
| take 10

Run in Playground

Output

_time hours_ago method status
2025-01-15T08:00:00Z 26 GET 200
2025-01-15T09:30:00Z 25 POST 201
2025-01-15T10:15:00Z 24 GET 404

This query computes the number of hours between each request and the current time, giving you a quick sense of how recent each event is.

Measure the number of minutes since each trace for the frontend service.

Query

['otel-demo-traces']
| extend minutes_since = datetime_diff('minute', now(), _time)
| where ['service.name'] == 'frontend'
| project _time, minutes_since, trace_id, duration

Run in Playground

Output

_time minutes_since trace_id duration
2025-01-15T09:00:00Z 1560 abc123 00:00:01.2340000
2025-01-15T09:05:00Z 1555 def456 00:00:00.8910000
2025-01-15T09:10:00Z 1550 ghi789 00:00:02.0050000

This query calculates how many minutes have elapsed since each frontend trace, useful for understanding the age of trace data.

Count error requests by how many days ago they occurred.

Query

['sample-http-logs']
| where toint(status) >= 400
| extend days_ago = datetime_diff('day', now(), _time)
| summarize error_count = count() by days_ago
| sort by days_ago asc

Run in Playground

Output

days_ago error_count
0 23
1 45
2 31

This query groups error responses by how many days ago they occurred, making it easy to spot whether error rates are increasing or decreasing over recent days.

  • datetime_add: Adds a specified number of date parts to a datetime. Use when you need to shift a timestamp rather than measure the gap between two.
  • ago: Subtracts a timespan from the current UTC time. Use for simple relative time filters.
  • now: Returns the current UTC time.
  • todatetime: Converts a value to a datetime. Use to parse strings into datetime values before computing differences.

Other query languages#

Splunk SPL users

In Splunk SPL, you typically calculate time differences using arithmetic on epoch timestamps, such as eval diff=round((_time - relative_time(now(), "-1d@d")) / 3600). In APL, the datetime_diff function directly computes the difference between two datetime values in a specified unit.

Splunk example

... | eval hours_diff=round((_time - relative_time(now(), "-1d@d")) / 3600)

APL equivalent

... | extend hours_diff = datetime_diff('hour', now(), _time)
ANSI SQL users

In ANSI SQL, you typically use DATEDIFF(hour, start_time, end_time) or TIMESTAMPDIFF(HOUR, start_time, end_time) to compute the difference between timestamps. In APL, datetime_diff follows a similar pattern with the unit as the first argument.

SQL example

SELECT DATEDIFF(hour, start_time, end_time) AS hours_diff FROM events;

APL equivalent

['dataset']
| extend hours_diff = datetime_diff('hour', end_time, start_time)

Updated

Was this page helpful?