tkhurana commented on code in PR #15: URL: https://github.com/apache/phoenix-site/pull/15#discussion_r3164732905
########## app/pages/_docs/docs/_mdx/(multi-page)/features/phoenix-row-timestamp.mdx: ########## @@ -0,0 +1,101 @@ +--- +title: "PHOENIX_ROW_TIMESTAMP()" +description: "Read the per-row last-modified timestamp Phoenix maintains automatically — usable in projections, predicates, and indexes for fast time-bounded reads." +--- + +`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](/docs/features/secondary-indexes#functional-indexes), which makes time-bounded reads fast even when the table isn't ordered by time. + +## Reading the row timestamp [#phoenix-row-timestamp-read] + +Project it like any other column: + +```sql +-- 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 [#phoenix-row-timestamp-where] + +Use it to bound queries by mutation time, including incremental "what changed +since" patterns: + +```sql +-- 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 scan of the table. The next section Review Comment: ```suggestion Without an index, these predicates require a full scan of the table. The next section ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
