Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-967

Consider (some of) SQL 2011 temporal support

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      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

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                apurtell Andrew Kyle Purtell
              • Votes:
                2 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: