Thursday, 5 February 2015

3 Types of Teradata Temporal Tables

There are 3 types of Temporal Tables.

1. Valid time temporal tables

2. Transaction time tables

3. Bi-Temporal tables

How to create Bi-temporal Tables:

CREATE MULTISET TABLE Asset_Owners
( Cust_No        INTEGER
 ,Asset_No        INTEGER
 ,Asset_Val_Time  PERIOD (DATE) NOT NULL as VALIDTIME
 ,Asset_Tran_Time PERIOD (TIMESTAMP(6) with TIME ZONE)
                      NOT NULL as TRANSACTIONTIME
)PRIMARY INDEX(Prop_No) ;


Why we call this as Bi-temporal table  means,we aliased one column as VALIDTIME and another column as TIME ZONE.

"PERIOD" is a new data-type, So it refers begin date and end date:

2015-01-01.2015-01-30

For time,

2015-01-01 08.09.000000-05.00.9999-12-31 08.09+05.30

So we understand that how data will be stored in PERIOD data type.

Keep reading more on Temporal tables.

No comments:

Post a Comment