System Functions (System Information)
Returns the timestamp in nanoseconds of the latest table version at or before the current offset for the specified stream. When the stream is queried (or consumed), the records returned include all transactions committed after this table version and before the current time.
This function was created primarily as a means to “bootstrap” a stream (i.e. return the set of records inserted between the period when the table was created (at table version
t0) and the specified stream was created). Since this function was introduced, CREATE STREAM and SELECT statements that include the CHANGES clause now support Time Travel using the AT | BEFORE clause. These options provide greater flexibility for querying historical table records.
The name of the stream to query.
Note that the entire name must be enclosed in single quotes, including the database and schema (if the name is fully-qualified), i.e.
If the stream name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, i.e.
This function returns an error when the input is a stream on a view.
To create a stream at or before the current offset for an existing stream, we recommend providing the existing stream name as input to the AT | BEFORE clause for simplicity and maximum compatibility with existing streams:
CREATE STREAM ... AT ( STREAM => '<stream-name>' )
Query the timestamp for the current offset for a stream:
create table MYTABLE1 (id int); create table MYTABLE2(id int); create or replace stream MYSTREAM on table MYTABLE1; insert into MYTABLE1 values (1); -- consume the stream begin; insert into MYTABLE2 select id from MYSTREAM; commit; -- return the current offset for the stream select system$stream_get_table_timestamp('MYSTREAM');