[jira] [Created] (PHOENIX-967) Consider (some of) SQL 2011 temporal support

["Andrew Purtell (JIRA)" <ji...@apache.org>]

Andrew Purtell created PHOENIX-967:
--------------------------------------

             Summary: Consider (some of) SQL 2011 temporal support
                 Key: PHOENIX-967
                 URL: https://issues.apache.org/jira/browse/PHOENIX-967
             Project: Phoenix
          Issue Type: New Feature
            Reporter: Andrew Purtell


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 





--
This message was sent by Atlassian JIRA
(v6.2#6252)