Details
-
New Feature
-
Status: Open
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
Description
Language extensions for temporal data were added in SQL 2011 (*1), providing among other things the ability to define period definitions as metadata to table, system-versioned tables (CREATE TABLE ... WITH SYSTEM VERSIONING), the ability to query by system version time, and syntax for conditions involving periods: CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES, and IMMEDIATELY SUCCEEDS.
While not proposing support for all of SQL 2011 temporal features, it seems that system versioning, the new temporal comparison operators, and the new SELECT syntax for versioned queries map pretty naturally to multiversoned HBase table schema and timerange filtering. And at least one commercial database supports temporal queries using an older nonstandard syntax (*2).
E.g. from *1:
- SELECT ... FOR SYSTEM_TIME AS OF TIMESTAMP <timestamp>...
- SELECT ... FOR SYSTEM_TIME BETWEEN TIMESTAMP <timestamp-start> AND TIMESTAMP <timestamp-end> ...
I didn't pay to download the 2011 standard doc so can't say if TIMESTAMP is required or if we can stand in any type that can be serialized to a long integer.
And from *2:
- SELECT ... AS OF TIMESTAMP <timestamp> ...
- SELECT ... VERSIONS BETWEEN TIMESTAMP <timestamp-start> AND <timestamp-end> ...
*1: http://www.sigmod.org/publications/sigmod-record/0612/publications/1209/pdfs/07.industry.kulkarni.pdf
*2: http://www.oracle.com/technetwork/database/storage/total-recall-whitepaper-171749.pdf
Attachments
Issue Links
- depends upon
-
CALCITE-715 Add PERIOD type constructor and period operators (CONTAINS, PRECEDES, etc.)
-
- Closed
-
- is related to
-
PHOENIX-914 Native HBase timestamp support to optimize date range queries in Phoenix
-
- Closed
-