Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
lukaseder opened this issue Jul 4, 2013 · 2 comments

Comments

@lukaseder
Copy link
Member

@lukaseder lukaseder commented Jul 4, 2013

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

This comment has been minimized.

Copy link

@delostilos delostilos commented Aug 2, 2014

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

This comment has been minimized.

Copy link
Member Author

@lukaseder lukaseder commented Aug 2, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.