# Date and time data types

[Read this page in HTML](https://www.scopedb.io/docs/reference/data-types/datetime)

Use `timestamp` for an instant in time and `interval` for a duration. Both have nanosecond precision.

## Timestamp

A `timestamp` represents a signed 64-bit count of nanoseconds from the Unix epoch (`1970-01-01T00:00:00Z`). Cast between `int` and `timestamp` to work with that count. Its range is `1677-09-21T00:12:43.145224192Z` through `2262-04-11T23:47:16.854775807Z`.

Timestamp strings can use [RFC 3339](https://www.rfc-editor.org/rfc/rfc3339) or [RFC 9557](https://www.rfc-editor.org/rfc/rfc9557.html) syntax. ScopeDB preserves the instant and displays it in UTC; the original time zone is not retained.

These checkout events arrive from producers in different time zones. Their timestamps represent the same instant:

```scopeql
VALUES
    ('web', '2026-06-01T10:00:00Z'),
    ('store', '2026-06-01T18:00:00+08:00'),
    ('support', '2026-06-01T12:00:00+02:00[Europe/Paris]')
SELECT $0 AS source, $1::timestamp AS event_time
```

```text
+---------+----------------------+
| source  | event_time           |
+---------+----------------------+
| web     | 2026-06-01T10:00:00Z |
| store   | 2026-06-01T10:00:00Z |
| support | 2026-06-01T10:00:00Z |
+---------+----------------------+
```

Use [`strftime`](https://www.scopedb.io/docs/reference/functions/date-and-time/format-parse.md) to format a timestamp in a chosen time zone, or [`strptime`](https://www.scopedb.io/docs/reference/functions/date-and-time/format-parse.md) to parse a custom text format.

## Interval

An `interval` is a signed duration with nanosecond precision, ranging from roughly -292 to +292 years. Cast between `int` and `interval` to work with its nanosecond count.

Duration strings accept hours and smaller units, such as `'24h'` for exactly 24 hours. Days, weeks, months, and years are not supported: intervals measure elapsed time, while calendar periods can vary in length. The range in years above is approximate.

Subtract timestamps to measure elapsed time. Add an interval to define a deadline:

```scopeql
VALUES ('2026-06-01T10:00:00Z', '2026-06-01T10:01:25Z')
SELECT $0::timestamp AS checkout_started, $1::timestamp AS payment_completed
SELECT
    payment_completed - checkout_started AS checkout_duration,
    checkout_started + 'PT5m'::interval AS checkout_deadline
```

```text
+-------------------+----------------------+
| checkout_duration | checkout_deadline    |
+-------------------+----------------------+
| 1m25s             | 2026-06-01T10:05:00Z |
+-------------------+----------------------+
```

Duration strings accept ISO 8601 notation or a compact form. For example, both forms below express a 500 ms threshold:

```scopeql
VALUES ('req-17', '120ms'), ('req-42', '750ms')
SELECT $0 AS request_id, $1::interval AS duration
SELECT
    request_id, duration,
    duration > 'PT0.5s'::interval AS over_iso,
    duration > '500ms'::interval AS over_compact
```

```text
+------------+----------+----------+--------------+
| request_id | duration | over_iso | over_compact |
+------------+----------+----------+--------------+
| req-17     | 120ms    | false    | false        |
| req-42     | 750ms    | true     | true         |
+------------+----------+----------+--------------+
```

## Related contents

* [Date and time functions](https://www.scopedb.io/docs/reference/functions/date-and-time.md)