Dates & times

date and timestamp columns carry the same capabilities as encrypted numbers — equality, ranges, ordering, MIN / MAX — but the queries they serve are temporal: time windows, newest-first listings, retention cutoffs, "when did this last happen".

Variants

Both types generate the same jsonb-backed domain variants. The generic form:

<T> is the encrypted type name from the table below, so public.eql_v3_<T>_ord is public.eql_v3_timestamp_ord for timestamp.

And every concrete domain this page covers:

Time columns are nearly always ranged and sorted, so _ord is the usual choice. Declare only the capability you query on — each capability stores extra searchable material with defined leakage (see Searchable encryption), and the variant model itself is covered in Core concepts.

Example

An audit-events table where the timestamps drive time-window queries and sorting:

CREATE TABLE audit_events (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at public.eql_v3_timestamp_ord,  -- time windows, newest-first, MIN/MAX
    review_due  public.eql_v3_date_ord,       -- range filters
    sealed_on   public.eql_v3_date            -- store and decrypt only
);

SEM specifiers

Both types take the same mechanism specifiers on their orderable variant (the concept is defined in Core concepts):

<T>_ord and <T>_ord_ope are byte-identical today. Pin _ord_ope when you want a column's mechanism frozen against a future change of the default.

Payload

A value for an _ord column carries the shared envelope keys (v, i, c — see Core concepts) plus the op ordering term. Here is a payload for the public.eql_v3_timestamp_ord occurred_at column:

{
  "v": 3,
  "i": { "t": "audit_events", "c": "occurred_at" },
  "c": "mBbKmsMM%bK#QQOx1yLDBHyD...",
  "op": "5f2b1a9e4c07d38b6ea15c92..."
}

• op is the only index term. It is a hex-encoded CLLW OPE ciphertext, which Postgres sorts by native bytea comparison. An _ord payload carries no hm, because ordering over a date or timestamp is equality-lossless: = and <> resolve against the same term.
• An _ord_ore payload carries ob in place of op: an array of block-ORE ciphertexts, 12 blocks for a timestamp.

Operators

Blocked operator cells raise an operator … is not supported exception — they never silently return wrong rows. ORDER BY is the one blocked cell that doesn't raise: it isn't an operator, so sorting a variant without an ordering term runs — but the order is meaningless (see Sorting). Operands must be typed ($1::public.eql_v3_timestamp_ord), or PostgreSQL resolves the native jsonb operator instead of the encrypted one. Both rules are covered in Core concepts.

Functions

Every operator has a function form, for managed platforms that disallow custom operators — same typed arguments, identical resolution. Each lists the encrypted domains it applies to; the MIN / MAX aggregates only exist as functions.

eql_v3.eq(a, b)

SELECT * FROM events
WHERE eql_v3.eq(occurred_at, $1::public.eql_v3_timestamp_eq);

eql_v3.neq(a, b)

SELECT * FROM events
WHERE eql_v3.neq(occurred_at, $1::public.eql_v3_timestamp_eq);

eql_v3.lt / lte / gt / gte

-- a range uses two of the four
SELECT * FROM events
WHERE eql_v3.gte(occurred_at, $1::public.eql_v3_timestamp_ord)
  AND eql_v3.lt(occurred_at, $2::public.eql_v3_timestamp_ord);

eql_v3.min(col)

-- compares ordering terms; result decrypts client-side
SELECT eql_v3.min(occurred_at) FROM events;

eql_v3.max(col)

SELECT eql_v3.max(occurred_at) FROM events;

Where to next