Temporal Validity in 12c

Temporal Validity enables you to track time periods for real world validity. Valid times can be set by users and applications for data, and data can be selected by a specified valid time, or a valid time range.

Concepts that are integral to valid time temporal modeling include:

  • Valid time: This is a user-defined representation of time. Examples of a valid time include project start and finish dates, and employee hire and termination dates.
  • Tables with valid-time semantics: These tables have one or more dimensions of user-defined time, each of which has a start and an end.
  • Valid-time flashback queries: This is the ability to do as-of and versions queries using a valid-time dimension.

A valid-time period consists of two date-time columns specified in the table definition. You can add a valid-time period by explicitly adding columns, or the columns can be created automatically. A valid-time period can be added during the create table or alter table process.

SQL> CREATE TABLE POS.EMP (ID NUMBER,
ENAME VARCHAR2(20),
SURNAME VARCHAR2(20),
START_DATA TIMESTAMP,
END_DATE TIMESTAMP,
PERIOD FOR EMPLOYEE_VALID_DATE(START_DATA, END_DATE));  

Table created.

SQL> COL OWNER FOR A10
SQL> COL TABLE_NAME FOR A20
SQL> COL COLUMN_NAME FOR A20
SQL> COL DATA_TYPE FOR A30
SQL> SET LINESIZE 200

SQL> SELECT owner, table_name, column_name, data_type, data_length, hidden_column, virtual_column, user_generated
FROM DBA_TAB_COLS
WHERE table_name='EMP' AND OWNER='POS'
ORDER BY column_id;  

OWNER      TABLE_NAME           COLUMN_NAME          DATA_TYPE                      DATA_LENGTH HID VIR USE
---------- -------------------- -------------------- ------------------------------ ----------- --- --- ---
POS        EMP                  ID                   NUMBER                                  22 NO  NO  YES
POS        EMP                  ENAME                VARCHAR2                                20 NO  NO  YES
POS        EMP                  SURNAME              VARCHAR2                                20 NO  NO  YES
POS        EMP                  START_DATA           TIMESTAMP(6)                            11 NO  NO  YES
POS        EMP                  END_DATE             TIMESTAMP(6)                            11 NO  NO  YES
POS        EMP                  EMPLOYEE_VALID_DATE  NUMBER                                  22 YES YES NO

6 rows selected.

SQL> INSERT INTO POS.EMP VALUES(1001,'Sam','Samreen','01-Jan-2018','31-March-2019');
INSERT INTO POS.EMP VALUES(1002,'JONES','KING','01-Jun-2019','29-Feb-2020');
INSERT INTO POS.EMP VALUES(1003,'JAMES','ALLEN','01-Feb-2019','30-Apr-2020');
INSERT INTO POS.EMP VALUES(1004,'MILLER','SMITH','01-Feb-2020','27-June-2021');

SQL> commit;

Commit complete.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MM-YYYY';

Session altered.

SQL> COL START_DATA FOR A15
SQL> COL END_DATE FOR A15
SQL> SELECT * FROM POS.EMP;

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1001 Sam                  Samreen              01-01-2018      31-03-2019
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021

Display list of employees who are valid until 31st of December 2019.

SQL> SELECT * FROM POS.EMP AS OF PERIOD FOR EMPLOYEE_VALID_DATE TO_TIMESTAMP('31-DEC-2019');

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020

Display list of employees who are valid until 1st of Feb 2020.

SQL> SELECT * FROM POS.EMP AS OF PERIOD FOR EMPLOYEE_VALID_DATE TO_TIMESTAMP('01-FEB-2020');

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1002 JONES                KING                 01-06-2019      29-02-2020
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021

Display list of employees who are valid until 1st of April 2020.

SQL> INSERT INTO POS.EMP VALUES(1005,'SCOTT','SMITH','01-MAR-2020',null);

1 row created.

SQL> commit;

SQL> SELECT * FROM POS.EMP AS OF PERIOD FOR EMPLOYEE_VALID_DATE TO_TIMESTAMP('01-APR-2020');

        ID ENAME                SURNAME              START_DATA      END_DATE
---------- -------------------- -------------------- --------------- ---------------
      1003 JAMES                ALLEN                01-02-2019      30-04-2020
      1004 MILLER               SMITH                01-02-2020      27-06-2021
      1005 SCOTT                SMITH                01-03-2020

Leave a Reply