timestamp_diff ↗

Use the timestamp_diff() function to calculate the numerical difference between two timestamps in a specified unit.

Syntax

timestamp_diff (<timestamp1>, <timestamp2>, <part>)

Parameters

Name Type Required Description
timestamp1 timestamp Yes The first timestamp object in the comparison.
timestamp2 timestamp Yes The second timestamp object, which is subtracted from the first.
part string Yes The unit in which the difference is expressed. Supported values are DAY, HOUR, MINUTE, SECOND, MILLISECOND, and MICROSECOND.

Returns

The timestamp_diff() function returns a numerical value (integer or float) representing the difference between the two timestamps in the specified unit.

Usage notes

  • The function calculates the difference by subtracting timestamp2 from timestamp1.
  • If timestamp1 is chronologically greater (later) than timestamp2, the function returns a positive value.
  • If timestamp1 is chronologically less (earlier) than timestamp2, the function returns a negative value.
  • If the calculated difference in the specified unit results in a fractional value between 0 and 1 (exclusive of 1, inclusive of 0), the function returns 0.
  • Supported values for the part parameter include: DAY, HOUR, MINUTE, SECOND, MILLISECOND, and MICROSECOND.

Examples

Example 1: Difference in seconds between current_time() and_time

Goal: Calculate the difference in seconds between the current query execution time and the event's timestamp.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter current_ts = current_time()
| alter event_ts = _time
| alter diff_in_seconds = timestamp_diff(current_ts, event_ts, "SECOND")
| fields event_id, event_ts, current_ts, diff_in_seconds
| limit 3

Explanation: The query computes the number of full seconds that have passed between the event's _time and the current_time() of the query execution.

Output:

EVENT_ID EVENT_TS CURRENT_TS DIFF_IN_SECONDS
101 Oct 26th 2023 10:00:00 Oct 26th 2023 12:00:00 7200
102 Oct 26th 2023 10:05:30 Oct 26th 2023 12:00:00 6870
103 Oct 26th 2023 10:15:15 Oct 26th 2023 12:00:00 6285

Example 2: Difference in minutes between _time and a static timestamp

Goal: Calculate the difference in minutes between the event timestamp and a specific static timestamp, demonstrating truncation.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter event_ts = _time
| alter static_past_ts = parse_timestamp("%Y-%m-%d %H:%M:%S", "2023-10-26 10:05:00")
| alter diff_in_minutes = timestamp_diff(event_ts, static_past_ts, "MINUTE")
| fields event_id, event_ts, static_past_ts, diff_in_minutes
| limit 3

Explanation: The query calculates the difference in minutes. Note that partial minutes (values between 0 and 1) are truncated to 0.

Output:

EVENT_ID EVENT_TS STATIC_PAST_TS DIFF_IN_MINUTES
101 Oct 26th 2023 10:00:00 Oct 26th 2023 10:05:00 -5
102 Oct 26th 2023 10:05:30 Oct 26th 2023 10:05:00 0
103 Oct 26th 2023 10:15:15 Oct 26th 2023 10:05:00 10

Example 3: Difference in hours, yielding a negative result

Goal: Calculate the difference in hours where the first timestamp is earlier than the second, resulting in a negative value.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter event_ts = _time
| alter static_future_ts = parse_timestamp("%Y-%m-%d %H:%M:%S", "2023-10-27 10:00:00")
| alter diff_in_hours = timestamp_diff(event_ts, static_future_ts, "HOUR")
| fields event_id, event_ts, static_future_ts, diff_in_hours
| limit 3

Explanation: The query demonstrates that when timestamp1 is earlier than timestamp2, the result is negative. Calculations are truncated to full hours.

Output:

EVENT_ID EVENT_TS STATIC_FUTURE_TS DIFF_IN_HOURS
101 Oct 26th 2023 10:00:00 Oct 27th 2023 10:00:00 -24
102 Oct 26th 2023 10:05:30 Oct 27th 2023 10:00:00 -23
103 Oct 26th 2023 10:15:15 Oct 27th 2023 10:00:00 -23

Example 4: Using timestamp_diff() in a filter stage

Goal: Filter events based on a calculated time difference relative to the current time.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter current_ts = current_time()
| filter timestamp_diff(current_ts, _time, "HOUR") > 1
| fields event_id, _time
| limit 3

Explanation: This query filters for events that occurred more than 1 hour prior to the current execution time.

Output:

EVENT_ID _TIME
101 Oct 26th 2023 10:00:00
102 Oct 26th 2023 10:05:30
103 Oct 26th 2023 10:15:15

Example 5: Difference in milliseconds between _time and a closely past static timestamp

Goal: Calculate a fine-grained difference in milliseconds between two close timestamps.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter event_ts = _time
| alter static_slightly_past_ts = parse_timestamp("%F %R:%E3S", "2023-10-26 10:00:00.000")
| alter diff_in_milliseconds = timestamp_diff(event_ts, static_slightly_past_ts, "MILLISECOND")
| fields event_id, event_ts, static_slightly_past_ts, diff_in_milliseconds
| limit 3

Explanation: The query calculates the exact difference in milliseconds, useful for precise time measurements.

Output:

EVENT_ID EVENT_TS STATIC_SLIGHTLY_PAST_TS DIFF_IN_MILLISECONDS
101 Oct 26th 2023 10:00:00 Oct 26th 2023 10:00:00 0
102 Oct 26th 2023 10:05:30 Oct 26th 2023 10:00:00 330000
103 Oct 26th 2023 10:15:15 Oct 26th 2023 10:00:00 915000