ROWTIME

ROWTIME is a system-generated column which returns the creation time of a stream row. Its type is always TIMESTAMP NOT NULL.

s-Server uses this system column to keep track of a stream’s time, as for windowed aggregation.

You can apply a column alias in order to override the system-generated ROWTIME with a timestamp from the data itself. This is known as promoting ROWTIME. Often, such a timestamp would be the time the row was generated by its source. Whether or not, or when in the pipeline, you do so depends on the analysis you are applying to your data. See the Concepts guide for more details.

For more details, see the topics Timestamp, ROWTIME, and CURRENT_ROW_TIMESTAMP in this guide.

ROWTIME System Column

When used in the SELECT clause of a streaming query, without being qualified by a preceding stream name, ROWTIME returns the timestamp of the row generated by the SELECT query itself.

Existing streams used as input for a SELECT query have their own ROWTIME columns. To reference these columns from within a query, qualify them with the stream name (or alias). The values of “unqualified” ROWTIME in a SELECT clause and the rowtime of an input stream can be different.

For example, the following query returns two separate rowtimes:

SELECT STREAM ROWTIME, s2.ROWTIME from s1, s2;

The first ROWTIME returns the creation time of the rows of the result stream.

The second, qualified, ROWTIME, returns the creation time of the rows of the input stream called s2.

ROWTIME s2.ROWTIME
=================== ===================
2020-02-20 10:15:00 2020-02-20 10:30:00
2020-02-20 10:25:00 2020-02-20 11:15:00
2020-02-20 10:25:30 2020-02-20 11:05:00

The difference between qualified and unqualified ROWTIME may affect your calculations, so it’s important to be aware of which one you’re referencing.

Rowtime and JDBC

Every streaming query has a ROWTIME column.

However, the ROWTIME column is not returned from a top-level JDBC query unless you explicitly include it in the SELECT clause. For example:

CREATE STREAM Orders(
"orderId" INTEGER NOT NULL,
"custId" INTEGER NOT NULL);
SELECT columnName
FROM ALL_STREAMS;

columnName
==========
orderId
custId

SELECT STREAM *
FROM Orders;

orderId custId
======= ======
100501
101 22
102699

SELECT STREAM ROWTIME, *
FROM Orders;

ROWTIME orderId custId
=================== ======= ======
2020-02-20 10:15:00 100501
2020-02-20 10:25:00 101 22
2020-02-20 10:25:30 102699

This is mainly to ensure compatibility with JDBC: since the stream Orders declares two columns, SELECT STREAM ** should return two columns.

Promoting a Column to ROWTIME

SQLstream assigns each row of a stream a timestamp value based on the streaming relational operators that created it. You can override that value by giving one column or expression in the query a column alias of ROWTIME.

For example, the following query returns rows with a constant timestamp:

SELECT STREAM
TIMESTAMP '1970-01-01 00:00:00' AS ROWTIME,
*
FROM Orders

It is not strictly necessary to use AS ROWTIME. s-Server promotes a column to the row’s timestamp column if its name, derived by the usual rules for column aliases, turns out to be ROWTIME. For example:

// s.ROWTIME implicitly becomes the timestamp of the generated row
SELECT STREAM
o.orderId,
s.ROWTIME
FROM Orders AS o
JOIN Shipments OVER (RANGE INTERVAL '1' HOUR FOLLOWING) AS s
ON o.orderId = s.orderId
// invalid, because no stream can have more than one ROWTIME column
SELECT STREAM
o.orderId,
o.ROWTIME,
s.ROWTIME
FROM Orders AS o
JOIN Shipments OVER (RANGE INTERVAL '1' HOUR FOLLOWING) AS s
ON o.orderId = s.orderId

SQLstream requires that rows have ascending timestamps. You may need to time sort data in order for it to work monotonically. For more information on t-sorting stream input, see the subtopic T-sorting Stream Input in the topic ORDER BY clause.

ROWTIME as a Column in a Table

It is illegal to create a stream with a column called ROWTIME, but it is acceptable for a column of a table to be called ROWTIME. (This is compatible with the SQL standard.)

Such a column has no special meaning, and can have any data type. The examples below illustrate the illegal use with a stream versus the valid use with a table.

However, this practice is likely to be confusing and is not recommended.

// fails: ROWTIME column clashes with system ROWTIME column
set schema 'test';

CREATE STREAM TooManyRowtime(
   foo INTEGER NOT NULL,
   rowtime TIMESTAMP NOT NULL
);
Error: java.lang.RuntimeException: ROWTIME is a reserved column name; encountered near line 3, column 1. (state=,code=0)

// succeeds, but definitely not recommended 

CREATE FOREIGN TABLE TableWithRowtime(
bar INTEGER NOT NULL,
rowtime VARCHAR(20)
)
SERVER FILE_SERVER
OPTIONS 
( "DIRECTORY" '/home/sqlstream/'
, "FILENAME_PATTERN" 'bar.csv'
, "PARSER" 'CSV' 
, "SKIP_HEADER" 'true'
);

;