Creating and Using a Table with Valid Time Support

View All Scripts Login to Run Script

Script Name Creating and Using a Table with Valid Time Support
Description 12c ; Example creates a table with Temporal Validity Support, inserts some rows, and issues some queries whose results depend on the valid start and end dates for individual rows.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Friday October 16, 2015

Statement 1

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))

Table created.

Statement 2

INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11')

1 row(s) inserted.

Statement 3

INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11')

1 row(s) inserted.

Statement 4

INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null)

1 row(s) inserted.

Statement 5

SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-10')

 
EMPNO LAST_NAME START_TIME END_TIME
100 Ames 01-JAN-10 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM

EMPNO	LAST_NAME	START_TIME	END_TIME
100	Ames	01-JAN-10 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM

Statement 6

SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-11')

 
EMPNO LAST_NAME START_TIME END_TIME
100 Ames 01-JAN-10 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM
101 Burton 01-JAN-11 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM

2 rows selected.

EMPNO	LAST_NAME	START_TIME	END_TIME
100	Ames	01-JAN-10 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM
101	Burton	01-JAN-11 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM

Statement 7

SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP( '01-Jul-11')

no data found

Statement 8

SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Feb-12')

 
EMPNO LAST_NAME START_TIME END_TIME
102 Chen 01-JAN-12 12.00.00.000000 AM  -

EMPNO	LAST_NAME	START_TIME	END_TIME
102	Chen	01-JAN-12 12.00.00.000000 AM	-

Statement 9

SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN  
   TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('02-Jun-10')

 
EMPNO LAST_NAME START_TIME END_TIME
100 Ames 01-JAN-10 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM

EMPNO	LAST_NAME	START_TIME	END_TIME
100	Ames	01-JAN-10 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM

Statement 10

SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN  
   TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('01-Mar-11')

 
EMPNO LAST_NAME START_TIME END_TIME
100 Ames 01-JAN-10 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM
101 Burton 01-JAN-11 12.00.00.000000 AM 30-JUN-11 12.00.00.000000 AM

2 rows selected.

EMPNO	LAST_NAME	START_TIME	END_TIME
100	Ames	01-JAN-10 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM
101	Burton	01-JAN-11 12.00.00.000000 AM	30-JUN-11 12.00.00.000000 AM

Statement 11

SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN  
   TO_TIMESTAMP('01-Nov-11') AND TO_TIMESTAMP('01-Mar-12')

 
EMPNO LAST_NAME START_TIME END_TIME
102 Chen 01-JAN-12 12.00.00.000000 AM  -

EMPNO	LAST_NAME	START_TIME	END_TIME
102	Chen	01-JAN-12 12.00.00.000000 AM	-

Statement 12

SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN  
   TO_TIMESTAMP('01-Jul-11') AND TO_TIMESTAMP('01-Sep-11')

no data found

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database