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
