DB Wiki

Create a period using existing columns

CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  PERIOD FOR student_module_period (start_date, end_date)
);

Create a period with system generated hidden columns

CREATE TABLE student_modules (
  id         NUMBER(10) NOT NULL,
  student_id NUMBER(10) NOT NULL,
  module_id  NUMBER(10) NOT NULL,
  start_date DATE,
  end_date   DATE,
  PERIOD FOR student_module_period
  );

We can now create AS OF PERIOD FOR queries.
The below query finds all students on a currently active course.

SELECT * FROM student_modules AS OF 
                 PERIOD FOR student_module_period SYSDATE sm;

Can also create VERSIONS PERIOD FOR … BETWEEN queries.

SELECT * FROM student_modules VERSIONS PERIOD FOR student_module_period BETWEEN
            TO_DATE('22-FEB-2015','DD-MON-YYYY') AND SYSDATE;