Timestamp function

timestamp(columnName) elects a designated timestamp:

Syntax#

During a CREATE operation#

Flow chart showing the syntax of the TIMESTAMP keyword

Create a designated timestamp column during table creation. For more information, refer to the CREATE TABLE section.

During a SELECT operation#

Flow chart showing the syntax of the timestamp function

Creates a designated timestamp column in the result of a query.

caution

The output query must be ordered by time. TIMESTAMP() does not check for order and using timestamp functions on unordered data may produce unexpected results.

tip

Dynamic timestamp allows to perform time series operations such as LATEST BY, SAMPLE BY or LATEST BY on tables which do not have a designated timestamp.

Examples#

During a CREATE operation#

The following creates a table with designated timestamp.

Create table
CREATE TABLE
temperatures(ts timestamp, sensorID symbol, sensorLocation symbol, reading double)
timestamp(ts);

During a SELECT operation#

The following will query a table and assign a designated timestamp to the output. Note the use of brackets to ensure the timestamp clause is applied to the result of the query instead of the whole readings table.

Dynamic timestamp
(SELECT cast(dateTime AS TIMESTAMP) ts, device, value FROM readings) timestamp(ts);

Although the readings table does not have a designated timestamp, we are able to create one on the fly. Now, we can use this into a subquery to perform timestamp operations.

Dynamic timestamp subquery
SELECT ts, avg(value) FROM
(SELECT cast(dateTime AS TIMESTAMP) ts, value FROM readings) timestamp(ts)
SAMPLE BY 1d;

If the data is unordered, it is important to order it first.

Dynamic timestamp - unordered data
SELECT ts, avg(value) FROM
(SELECT ts, value FROM unordered_readings ORDER BY ts) timestamp(ts)
SAMPLE BY 1d;