Date and time data types

On this page
Related contents

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 or RFC 9557 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:

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
+---------+----------------------+
| source  | event_time           |
+---------+----------------------+
| web     | 2026-06-01T10:00:00Z |
| store   | 2026-06-01T10:00:00Z |
| support | 2026-06-01T10:00:00Z |
+---------+----------------------+

Use strftime to format a timestamp in a chosen time zone, or strptime 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:

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
+-------------------+----------------------+
| 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:

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
+------------+----------+----------+--------------+
| request_id | duration | over_iso | over_compact |
+------------+----------+----------+--------------+
| req-17     | 120ms    | false    | false        |
| req-42     | 750ms    | true     | true         |
+------------+----------+----------+--------------+