> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/timestamp_diff.md).

# timestamp\_diff

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

## Syntax

```sql
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**:

```sql
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**:

```sql
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**:

```sql
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**:

```sql
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**:

```sql
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                 |

## Related articles

* **Stages**: [`alter`](/xql-command-reference-guide/readme/stages/alter.md), [`filter`](/xql-command-reference-guide/readme/stages/filter.md)
* **Functions**: [`current_time`](/xql-command-reference-guide/readme/functions/current_time.md), [`parse_timestamp`](/xql-command-reference-guide/readme/functions/parse_timestamp.md)
* **Datasets**: [`xdr_data`](https://www.google.com/search?q=%5Bhttps://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction%5D\(https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction\))


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/functions/timestamp_diff.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.