QuestDB offers the option to elect a column as
designated timestamp. This
allows you to leverage the high-performance time series functions of QuestDB,
but introduces a constraint on the column in question that will reject
- Only a
timestampcolumn can be
onecolumn can be elected for a given table.
designated timestampcan be elected either:
- during table creation.
- on the fly on sub-tables created within a query.
To elect a timestamp column on the fly, please refer to the dynamic timestamp documentation.
Once a column is elected as
designated timestamp, it will enforce an order
policy on this column. Inserts in
designated timestamp need to be incrementing
and out-of-order timestamps inserts will be rejected. This does not affect the
behaviour of other columns.
New timestamps need to be
greater or equal to the latest timestamp in the
designated timestamp allows you to:
- leverage timestamp partitions. For more information, refer to the partitions section.
- use time series joins such as
ASOF JOIN. For more information refer to the JOIN reference.
designated timestamp as a special column alongside other
existing timestamp columns. Note that:
designated timestampcolumn only allows ordered timestamps
- any other
timestampcolumn tolerates out-of-order timestamps
Attempts to insert
out-of-order timestamps will be rejected:
Working with timestamp order constraint
The constraint provides many benefits for both insert and query speed. However, it may be impractical in certain cases, for example when inserting values from multiple devices with slightly different clocks or network conditions. Luckily, there are ways to circumvent this with little overhead.
This is a temporary workaround. We are working on a table implementation which supports out-of-order insertion.
- Use the
database host clockas
designated timestampby using
For more information about
systimestamp() and related functions, check the
date & time functions section.
- Use a temporary table for the latest partition. Data can be out-of-order in this table.
When switching over to a new day, order the data in the temporary partition as it is inserted into the main table.