In a conventional database system, data stored in tables is considered valid at the present time. Because conventional tables are limited to a current snapshot of reality, they do not retain data that was present in the past or that will be valid in the future. A temporal database allows you to store data related to points in time by providing temporal data types and storing information related to the past, present, and future. For example, a table in a temporal database system can store data about inventory levels at the end of each month of the year. By storing the temporal dimensions of data in tables, you can query historical and prospective data trends.
A temporal table contains one or both of the following:
| Time | Description |
|---|---|
| ValidTime | Data modeled on the real world using a time period during which a fact (a row) is true (valid) in the real world. A value for the DATE or TIMESTAMP column in the table defines the time that a fact is known to be valid. |
| TransactionTime | Data using a time period beginning at the moment a fact (a row) is recorded (committed) in the database and ending when the fact is superseded through an update, rollback, or deletion. A value for the TIMESTAMP column in the table defines the transaction time of the fact. |
<table selection = "included"> <name>Employee_BT_1</name> <target_database> <name>MyDatabase</name> </target_database> </table>
If a force_utility is not specified, Data Mover automatically selects Teradata DSA when copying temporal tables. If DSA is not available, the Teradata PT update operator is selected. If you force the use of Teradata PT, the Teradata PT update operator is chosen as the copy method. When copying entire databases with DSA, all temporal tables on the source are copied to the target. When copying a temporal table, you can rename or relocate temporal tables on the target system or perform partial copies of the temporal tables.
<NONSEQUENCED VALIDTIME> <AND> <NONSEQUENCED TRANSACTIONTIME> SELECT * FROM Source_Table;Non-sequenced queries do not place any special semantics on temporal columns, treating them as any other regular columns. Therefore all current, history, and future rows that are open or closed are copied to the target.
The primary key constraint of a temporal table is maintained through a system-defined join index. This system-defined join index is automatically generated by Analytics Database when the table is created. When copying a temporal table, you do not need to separately copy the system-defined join index. DSA copies the system-defined join index setting. For other copy methods, the system-defined join index already exists on the target if the table already exists or it is automatically generated when the table is created as part of the job.
- Copying using the TPT API LOAD operator when the allowTPtLoadForMultiset attribute is not set to true.
- Copying the temporal table and its system-defined join index as as a separate item rather than copying only the temporal table.