Add support for Oracle 12c's temporal validity (AS OF PERIOD FOR)

[lukaseder]

Similar to the existing flashback query clause (#1079), there is now also support for temporal validity in regular data, without Oracle's flashback feature:

An example taken from the documentation

CREATE TABLE my_emp(
  empno NUMBER,
  last_name VARCHAR2(30),
  start_time TIMESTAMP,
  end_time TIMESTAMP,
PERIOD FOR user_valid_time (start_time, end_time));

INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11');
INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11');
INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null);

-- Returns only Ames.
SELECT * from my_emp 
    AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-10');

-- Returns  Ames and Burton, but not Chen.
SELECT * from my_emp 
    AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-11');

-- VERSIONS PERIOD FOR ... BETWEEN queries:
-- Returns only Ames.
SELECT * from my_emp 
    VERSIONS PERIOD FOR user_valid_time 
    BETWEEN TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('02-Jun-10');

See the documentation here:
http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#CACHEBFC

[delostilos]

Hi,

Found a nice blog entry that gives an overview of the temporal support in Oracle 12c http://www.salvis.com/blog/2014/01/04/multi-temporal-database-features-in-oracle-12c/

This presentation called "Towards a temporal PostgeSQL" gives an overview of temporal implementations of other DB vendors at slides 8-13 and afterwards the proposed solution in PostgreSQL http://www.slideshare.net/SFScon/slides-28283464

Regards,
JJ

[lukaseder]

Thanks a lot @delostilos, those are very interesting articles / presentations! We'll have to be reviewing the SQL:2011 standards as well, as the <period predicate> has been introduced:

<period predicate> ::=
    <period overlaps predicate>
  | <period equals predicate>
  | <period contains predicate>
  | <period precedes predicate>
  | <period succeeds predicate>
  | <period immediately precedes predicate>
  | <period immediately succeeds predicate>

as well as:

<table period definition> ::=
  <system or application time period specification>
    <left paren> <period begin column name> <comma> <period end column name> <right paren>

<system or application time period specification> ::=
    <system time period specification>
  | <application time period specification>

<system time period specification> ::=
  PERIOD FOR SYSTEM_TIME

<application time period specification> ::=
  PERIOD FOR <application time period name>

So, this isn't strictly an Oracle feature. IBM DB2 also has a lot of new improvements called "time travel query":
http://www-01.ibm.com/software/data/db2/linux-unix-windows/time-travel-query.html

[lukaseder]

Duplicate of #4704