search

prestodb / presto

The official home of the Presto distributed SQL query engine for big data
http://prestodb.io
Apache License 2.0
16.08k stars 5.39k forks source link

Support time travel range queries ( FROM/TO , BETWEEN/AND) #22026

Open gupteaj opened 9 months ago

gupteaj commented 9 months ago

Presto has added time travel feature for iceberg tables to allow point-in-time query using AS OF syntax. This feature request will allow presto to run range queries based on two different snapshot ids or timestamps

Presto issue for time travel feature - https://github.com/prestodb/presto/issues/20495

Expected Behavior or Use Case

// Time travel range query cases 

select * from tab1 FOR SYSTEM_VERSION BETWEEN 8772871542276440693 AND 9552871542276440765;

select * from tab1 FOR SYSTEM_VERSION FROM 8772871542276440693 TO 9552871542276440765;

select * from tab1 FOR SYSTEM_TIME BETWEEN TIMESTAMP '2023-08-17 13:29:46.822 America/Los_Angeles' AND  TIMESTAMP '2023-12-12 13:29:46.822 America/Los_Angeles';

select * from tab1 FOR SYSTEM_TIME FROM TIMESTAMP '2023-08-17 13:29:46.822 America/Los_Angeles' TO TIMESTAMP '2023-12-12 13:29:46.822 America/Los_Angeles';

Context

Many databases such as SQL server, IBM DB2 , IBM Netezza support this feature.

SQL Server - https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16

IBM Netezza - https://cloud.ibm.com/docs/netezza?topic=netezza-runningqueries_tt#fromtobetweenand_tt

IBM DB2 - https://www.ibm.com/docs/en/db2/11.5?topic=tables-querying

imjalpreet commented 2 months ago

@gupteaj @tdcmeehan Hi, is anyone working on this currently? If not, I am interested in working on this.