Live SQLSign In
Creating and Using a Table with Valid Time Support
View All Scripts Login and Run Script
Script NameCreating and Using a Table with Valid Time Support
Description12c ; 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.
CategorySQL General
ContributorMike Hichwa (Oracle)
CreatedThursday October 15, 2015
Statement
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
INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11')
1 row(s) inserted.
Statement
INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11')
1 row(s) inserted.
Statement
INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null)
1 row(s) inserted.
Statement
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01Jun-10')
EMPNO
100
Statement
LAST_NAME
Ames
START_TIME
01-JAN-10 12.00.00.000000 AM
END_TIME
30-JUN-11 12.00.00.000000 AM
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01Jun-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.
Statement
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP( '01Jul-11')
no data found
Statement
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01Feb-12')
EMPNO
LAST_NAME
102
Chen
START_TIME
END_TIME
01-JAN-12 12.00.00.000000 AM
Statement
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('02-Jun-10')
EMPNO
100
Statement
LAST_NAME
Ames
10
START_TIME
01-JAN-10 12.00.00.000000 AM
END_TIME
30-JUN-11 12.00.00.000000 AM
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.
11
Statement
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
102
Chen
START_TIME
01-JAN-12 12.00.00.000000 AM
END_TIME
-
12
Statement
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 OTNSQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials
2016 Oracle Corporation
Privacy | Terms of Use
