Thanks Feng, it's exciting to have this ability. Regarding the syntax section, are you proposing `AS OF` instead of `FOR SYSTEM AS OF` to do this? I know `FOR SYSTEM AS OF` is in the SQL standard and has been supported in some database vendors such as SQL Server. About `AS OF`, is it in the standard or any database vendor supports this, if yes, I think it's worth to add this support to Calcite, and I would give a hand in Calcite side. Otherwise, I think we'd better to use `FOR SYSTEM AS OF`.
Timo Walther <twal...@apache.org> 于2023年5月25日周四 19:02写道: > Also: How do we want to query the most recent version of a table? > > `AS OF CURRENT_TIMESTAMP` would be ideal, but according to the docs both > the type is TIMESTAMP_LTZ and what is even more concerning is the it > actually is evalated row-based: > > > Returns the current SQL timestamp in the local time zone, the return > type is TIMESTAMP_LTZ(3). It is evaluated for each record in streaming > mode. But in batch mode, it is evaluated once as the query starts and > uses the same result for every row. > > This could make it difficult to explain in a join scenario of multiple > snapshotted tables. > > Regards, > Timo > > > On 25.05.23 12:29, Timo Walther wrote: > > Hi Feng, > > > > thanks for proposing this FLIP. It makes a lot of sense to finally > > support querying tables at a specific point in time or hopefully also > > ranges soon. Following time-versioned tables. > > > > Here is some feedback from my side: > > > > 1. Syntax > > > > Can you elaborate a bit on the Calcite restrictions? > > > > Does Calcite currently support `AS OF` syntax for this but not `FOR > > SYSTEM_TIME AS OF`? > > > > It would be great to support `AS OF` also for time-versioned joins and > > have a unified and short syntax. > > > > Once a fix is merged in Calcite for this, we can make this available in > > Flink earlier by copying the corresponding classes until the next > > Calcite upgrade is performed. > > > > 2. Semantics > > > > How do we interpret the timestamp? In Flink we have 2 timestamp types > > (TIMESTAMP and TIMESTAMP_LTZ). If users specify AS OF TIMESTAMP > > '2023-04-27 00:00:00', in which timezone will the timestamp be? We will > > convert it to TIMESTAMP_LTZ? > > > > We definely need to clarify this because the past has shown that > > daylight saving times make our lives hard. > > > > Thanks, > > Timo > > > > On 25.05.23 10:57, Feng Jin wrote: > >> Hi, everyone. > >> > >> I’d like to start a discussion about FLIP-308: Support Time Travel In > >> Batch > >> Mode [1] > >> > >> > >> Time travel is a SQL syntax used to query historical versions of data. > It > >> allows users to specify a point in time and retrieve the data and > >> schema of > >> a table as it appeared at that time. With time travel, users can easily > >> analyze and compare historical versions of data. > >> > >> > >> With the widespread use of data lake systems such as Paimon, Iceberg, > and > >> Hudi, time travel can provide more convenience for users' data analysis. > >> > >> > >> Looking forward to your opinions, any suggestions are welcomed. > >> > >> > >> > >> 1. > >> > https://cwiki.apache.org/confluence/display/FLINK/FLIP-308%3A+Support+Time+Travel+In+Batch+Mode > >> > >> > >> > >> Best. > >> > >> Feng > >> > > > > -- Best, Benchao Li