Time Travel

Batch Streaming

The syntax of time travel is used for querying historical data. It allows users to specify a point in time and query the corresponding table data.

Attention Currently, time travel requires the corresponding catalog that the table belongs to implementing the getTable(ObjectPath tablePath, long timestamp) method. See more details in Catalog.

The syntax with time travel clause is:

  1. SELECT select_list FROM table_name FOR SYSTEM_TIME AS OF timestamp_expression

Parameter Specification:

  • FOR SYSTEM_TIME AS OF timestamp_expression：Used to query data at a specific point in time, the timestamp_expression represents the historical time point you want to query. The timestamp_expression can be a specific timestamp or a time-related expression that can be reduced to a constant, and this expression can only apply to physical tables and not to views or sub-queries.

Example

  1. --use timestamp constant expression
  2. SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TIMESTAMP '2023-07-31 00:00:00'
  4. --use expression with functions that can be reduced to a timestamp constant
  5. SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TIMESTAMP '2023-07-31 00:00:00' - INTERVAL '1' DAY

Limitation

Attention The timestamp_expression used in time travel only supports certain types of expressions that can be reduced to TIMESTAMP constants, including constant expressions of type TIMESTAMP, adding and subtracting operations on timestamps, as well as some partial built-in functions and UDFs.

When UDFs are used in a timestamp_expression, due to the limitations of the current framework, some expressions cannot be directly reduced to a TIMESTAMP constant during SQL parsing and an exception will be thrown.

  1. --use expression with functions that can not be reduced to a timestamp constant
  2. SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TO_TIMESTAMP_LTZ(0, 3)

The corresponding exceptions are as follows:

  1. Unsupported time travel expression: TO_TIMESTAMP_LTZ(0, 3) for the expression can not be reduced to a constant by Flink.

Time Zone Handling

The data type generated by the TIMESTAMP expression is TIMESTAMP type, but there’s a special case in the time travel clause. When encountering the time travel clause, the framework will convert the TIMESTAMP type to the LONG type based on the local time zone. Therefore, the results of the same time travel query statement may vary in different time zones.