Hi all, We are looking for the temporal support in Calcite, or so-called system-versioned temporal table. The temporal database feature was introduced in ANSI SQL 2011 [1] and was supported in SQL Server 2017 [2]. A system-versioned temporal table is designed to keep a full history of data changes and allow easy point in time analysis. It is very useful for scenarios that require tracking history of data changes.
Because system-versioned tables are intended primarily for tracking historical data changes, queries on system-versioned tables often tend to be concerned with retrieving the table content as of a given point in time or between any two given points in time. SQL:2011 provides FOR SYSTEM_TIME AS OF syntactic extension for this specific purpose. For example, the following query retrieves the rows of Emp that were current as of Jan. 2, 2011: SELECT ENo,EName,Sys_Start,Sys_End FROM Emp FOR SYSTEM_TIME AS OF TIMESTAMP '2011-01-02 00:00:00' In addition, we need the time expression can be a relational expression whose value is from another table. For example, the following query joins the Orders to the Prices as the price was at the order time: SELECT STREAM * FROM Orders AS o JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF o.orderTime AS p ON o.productId = p.productId So I would like to introduce the syntactic extension in Calcite. What do you think about this? Any comments or suggestions are welcome! [1] https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf [2] https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables [3] https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table [4] http://sqlhints.com/tag/for-system_time-as-of/ Bests, Jark Wu
