Chapter 8
Temporal Tables Create Functions
“One thing you can't recycle is wasted time.”
Table of Contents Chapter 8 – Temporal Tables Create Functions
– Three types of Temporal Tables
– CREATING a Bi-Temporal Table
– Bi-Temporal Data Type Standards
– Bi-Temporal Example – Tera-Tom buys!
– A Look at the Temporal Results
– Bi-Temporal Example – Tera-Tom Sells!
– Bi-Temporal Example – How the data looks!
– Normal SQL for Bi-Temporal Tables
– NONSEQUENCED SQL for Temporal Tables
– AS OF SQL for Temporal Tables
– Bi-Temporal Example – Socrates is DELETED!
– Property_Owners Before DELETE on April 1st
Three types of Temporal Tables
The Three types of Temporal Tables are:
Valid Time Temporal Tables
Transaction Time Temporal Tables
Bi-Temporal Tables containing both Valid Time and Transaction Time.
Temporal Tables use a Valid Time or Transaction Time or combine both Valid Time and Transaction Time to form Bi-Temporal tables.
CREATING a Bi-Temporal Table
CREATE MULTISET TABLE Property_Owners
( Cust_No INTEGER
,Prop_No INTEGER
,Prop_Val_Time PERIOD (DATE) NOT NULL as VALIDTIME
,Prop_Tran_Time PERIOD (TIMESTAMP(6) with TIME ZONE)
NOT NULL as TRANSACTIONTIME
) PRIMARY INDEX(Prop_No) ;
This is a Bi-Temporal Table because one column is aliased VALIDTIME and another column is aliased TRANSACTIONTIME. This makes this table a Bi-Temporal Table.
PERIOD Data Types
A new data type PERIOD has been introduced. This means two dates (a begin and end date) or it could be two Timestamps (a begin and ending Timestamp).
Bi-Temporal Data Type Standards
CREATE MULTISET TABLE Property_Owners
(
Cust_No INTEGER
,Prop_No INTEGER
,Prop_Val_Time PERIOD (DATE) NOT NULL as VALIDTIME
,Prop_Tran_Time PERIOD (TIMESTAMP(6) with TIME ZONE)
NOT NULL as TRANSACTIONTIME
)
PRIMARY INDEX(Prop_No) ;
What PERIOD Data Types do ValidTime and TransactionTime require?
• ValidTime can be either a date or a Timestamp
• TransactionTime must be a Timestamp written exactly as above!
The example above is perfect for your PERIOD Data type for TRANSACTIONTIME. You have options for the VALIDTIME, as it can be either a Date or Timestamp.
Bi-Temporal Example – Tera-Tom buys!
CREATE MULTISET TABLE Property_Owners
(
Cust_No INTEGER
,Prop_No INTEGER
,Prop_Val_Time PERIOD (DATE) NOT NULL as VALIDTIME
,Prop_Tran_Time PERIOD (TIMESTAMP(6) with TIME ZONE)
NOT NULL as TRANSACTIONTIME
)
PRIMARY INDEX(Prop_No) ;
INSERT INTO PROPERTY_OWNERS
(Cust_No, Prop_No)
VALUES (1, 100) ;
On January 1, 2011 Tera-Tom buys property 100 which is beach front property. Tera-Tom is Cust_No 1 in your table and number 1 in your heart.
A Look at the Temporal Results
INSERT INTO PROPERTY_OWNERS
(Cust_No, Prop_No)
VALUES (1, 100) ;
Below is what the table looks like internally
On January 1, 2011 Tera-Tom buys property 100 and this is what the Bi-Temporal table looks like. Notice the 9999-12-31 dates. That means this is an OPEN Date.
Bi-Temporal Example – Tera-Tom Sells!
UPDATE Property_Owners
SET Cust_No = 2
WHERE Prop_No = 100 ;
How will the table change below after the UPDATE?
On January 1, 2011 Tera-Tom buys property 100 and then Tera-Tom sells to Socrates (Cust_No 2) on February 14th, 2011.
Bi-Temporal Example – How the data looks!
Property_Owners Before Update
Property_Owners After Update
Here is how the new table looks like with three rows. In the bottom table example there is only 1-row that is still open. Do you know which one? The last one!
Normal SQL for Bi-Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. You will see a wide variety of SQL Keywords before the real SQL starts. The first is normal SQL.
NONSEQUENCED SQL for Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. Here is a look at the keyword NONSEQUENCED. This brings back all customers.
AS OF SQL for Temporal Tables
Property_Owners Table
It is special SQL that allows Bi-Temporal tables to work so effectively. The AS OF DATE ‘2011-01-30’ reports the state of Property_Owners on that exact date.
NONSEQUENCED for Both
NONSEQUENCED VALIDTIME
AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM Property_Owners ;
It is special SQL that allows Bi-Temporal tables to work so effectively. Above is NONSEQUENCED VALIDTIME and NONSEQUENCED TRANSACTIONTIME.
Bi-Temporal Example – Socrates is DELETED!
DELETE FROM Property_Owners
WHERE Prop_No = 100 ;
How will the table change below after the DELETE?
Property_Owners Before DELETE
On April Fools day, April 1, 2011 Socrates sells the property, but through another Mortgage company, so since the mortgage company no longer owns the property, Socrates is DELETED. How will the table look after the Delete.
Property_Owners Before DELETE on April 1st
Property_Owners AFTER DELETE on April 1st
Here is the table and it has no Open Rows. The bold red shows why the row is closed.