datetime_diff
This page explains how to use the datetime_diff function in APL.
Use the datetime_diff function in APL to calculate the calendarian difference between two datetime values in a specified unit. The function computes datetime1 - datetime2 and returns the result as a count of the specified date part.
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 10Output
| _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, durationOutput
| _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 ascOutput
| 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.
List of related functions
- 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.