PHOENIX_ROW_TIMESTAMP()

PHOENIX_ROW_TIMESTAMP() is a built-in SQL function that returns the timestamp of the row's empty column, which Phoenix updates automatically on every write. It's effectively the row's last-modified time, available on any Phoenix table without you having to declare or manage a timestamp column yourself. The return type is DATE.

It can be used in three places, and the third one is what makes it especially powerful:

1. As a projection in SELECT.
2. As a predicate in WHERE (and JOIN) clauses.
3. As the indexed expression in a functional index, which makes time-bounded reads fast even when the table isn't ordered by time.

Reading the row timestamp

Project it like any other column:

-- Last-modified time of every row.
SELECT PHOENIX_ROW_TIMESTAMP(), id, payload FROM events;

-- Combine with regular row data.
SELECT id,
       PHOENIX_ROW_TIMESTAMP() AS modified_at,
       payload
FROM events
WHERE region = 'us-west-2';

The function takes no arguments and is evaluated server-side from the empty cell that Phoenix already maintains for every row.

In WHERE predicates

Use it to bound queries by mutation time, including incremental "what changed since" patterns:

-- Rows modified in the last hour.
SELECT * FROM events
WHERE PHOENIX_ROW_TIMESTAMP() > CURRENT_DATE() - 1.0 / 24;

-- Pull a window for an incremental consumer.
SELECT id, payload
FROM events
WHERE PHOENIX_ROW_TIMESTAMP() >= ?
  AND PHOENIX_ROW_TIMESTAMP() <  ?
ORDER BY PHOENIX_ROW_TIMESTAMP() ASC;

Without an index, these predicates require a full scan of the table. The next section fixes that.

Indexing on PHOENIX_ROW_TIMESTAMP()

Create a functional index on the function to make time-bounded reads fast. Phoenix can then seek directly on the indexed timestamp instead of scanning the data table:

CREATE INDEX events_by_modified
    ON events (PHOENIX_ROW_TIMESTAMP())
    INCLUDE (payload);

-- Phoenix uses the index for this query.
SELECT id, payload
FROM events
WHERE PHOENIX_ROW_TIMESTAMP() >= ?
  AND PHOENIX_ROW_TIMESTAMP() <  ?
ORDER BY PHOENIX_ROW_TIMESTAMP() ASC;

This is the typical recipe for "scan rows changed in the last N minutes" type queries on a table whose primary key isn't time-ordered. Pair with uncovered indexes when you don't want to duplicate payload columns into the index.

If your downstream needs a continuous, ordered stream of changes rather than periodic time-bounded scans — including the actual mutation deltas — reach for Change Data Capture instead. The functional index pattern here is best suited to ad-hoc time-window reads from the same client that issues regular SQL queries.

Relationship to ROW_TIMESTAMP column

Two related concepts share the word "timestamp" — they are not the same:

PHOENIX_ROW_TIMESTAMP() works on any Phoenix table, regardless of whether you've declared a ROW_TIMESTAMP column. If you have declared one, both mechanisms read/write the same underlying timestamp, so PHOENIX_ROW_TIMESTAMP() returns the value of the ROW_TIMESTAMP column for that row.