ROWTIME

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  >

ROWTIME

Previous pageReturn to chapter overviewNext page

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      

=================== ===================

2008-02-20 10:15:00 2008-02-20 10:30:00

2008-02-20 10:25:00 2008-02-20 11:15:00

2008-02-20 10:25:30 2008-02-20 11:05:00

 

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

======= ======

   100    501

   101     22

   102    699

 

SELECT STREAM ROWTIME, *

FROM Orders;

 

ROWTIME             orderId custId

=================== ======= ======

2008-02-20 10:15:00     100    501

2008-02-20 10:25:00     101     22

2008-02-20 10:25:30     102    699

 

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

Promoting 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. See the topic T-sorting Stream Input in this guide for more details.

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

CREATE STREAM TooManyRowtime(

 foo INTEGER NOT NULL,

 rowtime TIMESTAMP NOT NULL);

// succeeds, but not recommended

CREATE TABLE TableWithRowtime(

 bar INTEGER NOT NULL,

 rowtime VARCHAR(20));