westonpace opened a new issue, #2006: URL: https://github.com/apache/datafusion-sqlparser-rs/issues/2006
Several databases and data lakes support time travel which allows historical versions of a table to be queried. Regrettably, syntax and capabilities do not seem to be terribly consistent across the various implementations. ## Querying Historical Times This option lets a database be queried as it existed at a certain point in time. (*Question:* Is this "last version prior to the given time"? or is it an error if no snapshot exists with that exact time?) ### [Databricks](https://docs.databricks.com/gcp/en/delta/history#delta-time-travel-syntax) ``` SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z'; ``` ### [Snowflake](https://docs.snowflake.com/en/sql-reference/constructs/at-before) ``` SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::TIMESTAMP_LTZ); ``` ### [Cockroach](https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/) ``` SELECT * FROM t AS OF SYSTEM TIME '2016-06-15 12:45:00' ``` ### [DuckLake](https://ducklake.select/docs/stable/duckdb/usage/time_travel.html) ``` SELECT * FROM tbl AT (TIMESTAMP => now() - INTERVAL '1 week'); ``` ## Querying Specific versions This options lets a specific "version" of a database to be queried. What exactly this means seems to depend on the database implementation in question. ### Databricks ``` SELECT * FROM people10m VERSION AS OF 123; ``` ### Snowflake Note: unclear if this is the same thing (specific versions) or different ``` SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726'); ``` ### Cockroach Unsupported ### DuckLake ``` SELECT * FROM tbl AT (VERSION => 3); ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
