Time Travel #
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.
The syntax with time travel clause is:
SELECT select_list FROM table_name FOR SYSTEM_TIME AS OF timestamp_expression
FOR SYSTEM_TIME AS OF timestamp_expression：Used to query data at a specific point in time, the
timestamp_expressionrepresents the historical time point you want to query. The
timestamp_expressioncan 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.
--use timestamp constant expression SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TIMESTAMP '2023-07-31 00:00:00' --use expression with functions that can be reduced to a timestamp constant SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TIMESTAMP '2023-07-31 00:00:00' - INTERVAL '1' DAY
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.
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.
--use expression with functions that can not be reduced to a timestamp constant SELECT select_list FROM paimon_tb FOR SYSTEM_TIME AS OF TO_TIMESTAMP_LTZ(0, 3)
The corresponding exceptions are as follows:
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.