Identifying Temporal Tables


Identifying Temporal Tables

Is there any way to identify temporal tables using data dictionary (DBC) tables in TD 13.10 and above?


Tags (1)

Re: Identifying Temporal Tables

Hi Kishore_1,

I am glad that your period data type problem is resolved. There are many functions  available in teradata temporal features.

I am not able to see any indicator in any dictionary table.

My suggestion, is that , if we have many temporal tables, better place them in a separate database for structured maintenance, ease of management etc. Also we can  trace temporal from  dbc.tvm, requesttext.

I see that implementation of temporal features is few and far between.

From my standpoint, I see that having a separate indicator for this in dictionary table, may or may not be good. It is just my opinion.



Re: Identifying Temporal Tables

In 14.10 the dbc documentation states that tables views should have the column


Further down you find the following description:

TemporalProperty Column

The TemoralProperty column indicates that the table, view, or join index is a nontemporal, ValidTime, TransactionTime, or bi-temporal table.


Re: Identifying Temporal Tables



Plz doubke check. I am on 14.10.


create set table retail.temp1(id integer, dob period(date) format 'YYYY-MM-DD');


SELECT * FROM DBC.COLUMNS WHERE DATABASENAME='retail' and tablename='temp1',

then you will see columntype='PD' ------PD PERIOD(DATE)


Similarly, you can get dob period(timestamp)

columntype=PS     PERIOD(TIMESTAMP (n))


However, you may need to join dbc.columns with dbc.tables to filter only for tables only.


period data type







Re: Identifying Temporal Tables

The column Ulrich mentioned exists since TD13.10, so to get all Temporal tables:

SELECT * FROM dbc.TablesV WHERE TemporalProperty <> 'N'

Similar to find the Temporal columns:

SELECT * FROM dbc.ColumnsV WHERE TimeDimension <> 'N'

Re: Identifying Temporal Tables

Thanks Ulrich,Raja and  Dieter for the valuable information on identifying temporal tables in Teradata.

Could you please help me in answering: Which DBS control parameter(s) should be enabled to enable this feature on TD13.10 and above?

Does it require a system restart?

Teradata Employee

Re: Identifying Temporal Tables

On a real Teradata system, this requires a change control and verification of entitlement. (Yes, it requires a restart.) Talk to your local Teradata sales and Customer Services team for assistance.

For Teradata Express VMs, the feature should be enabled.