Temporal Data Types for Teradata :: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Overview

Teradata provides built-in support for temporal data. Temporal data makes it easy to build and maintain applications where information changes over time. For example, consider a merchandise application that must store the price of an item along with the dates during which that price is valid. As shown below, traditional table design uses two date columns to store the beginning and end dates during which the price is valid.

CREATE TABLE price
( 
  Item_UPC    BIGINT,
  Price       FLOAT,
  Begin_date  DATE,
  End_Date    DATE
)

Item_UPC			Price		Begin_Date		End_Date
--------			-----		----------		--------
123456789012	   $5		2010-01-05		9999-12-31

When the price of the item changes, the end date of the current price must be updated, and a new row must be inserted with the new price.

Item_UPC			Price		Begin_Date		End_Date
--------			-----		----------		--------
123456789012		$5		2010-01-05		2011-05-01
123456789012		$4		2011-05-01		9999-12-31

Teradata temporal support can simplify this process. You can use the PERIOD(DATE) data type to represent the time period during which the price is valid. Here is how you can create the table instead.

CREATE TABLE price
( 
  Item_UPC       BIGINT,
  Price          FLOAT,
  Price_validity PERIOD(DATE) NOT NULL AS VALIDTIME
)

Item_UPC			Price		Price_validity
--------			-----		--------------
123456789012	   $5		(2010-01-05, 9999-12-31)

For additional examples and information about features, see the Teradata temporal table support documentation.

Supported Temporal Data Types

SAS/ACCESS Interface to Teradata supports these temporal data types.

PERIOD(TIME)
PERIOD(DATE)
PERIOD(TIMESTAMP)
PERIOD(DATE) VALIDTIME/TRANSACTIONTIME
PERIOD(TIMESTAMP) VALIDTIME/TRANSACTIONTIME

Specifying Transaction Time and Valid Time

For true temporal support, you must specify the transaction-time and valid-time attributes on the PERIOD data type.

The transaction-time attribute on a PERIOD column makes the table a transaction-time table. Teradata automatically maintains tables with transaction-time columns. It tracks when a row is first made known to the table. When a row is inserted, it is considered to be an open row because it is currently in effect until the end of time. If the row is deleted, Teradata marks it as a closed row that is no longer in effect. However, the table can be queried to obtain rows that were open at a particular point in time even though the row is not currently valid. Similarly, when a row is modified, the current row is closed and a new row is opened and made effective.

A user cannot set or modify a transaction-time column.

The valid-time attribute indicates the time period during which the information is in effect. If valid time is specified with the PERIOD data type, Teradata maintains how the time period is in effect if the row is updated or deleted. As in the example about prices, when a row is inserted with a new price, Teradata maintains the end date of the original row. The row with the old price is updated with an end date and the new row is inserted.

A row in a valid-time transaction table can be a history row, a current row, or a future row. The history row is no longer valid with respect to current time. Its end-time period is before the current time. A current row has a time period that straddles the current time.

Creating a Table from SAS with the PERIOD Data Type

To create a Teradata table with temporal data types from SAS, use the DBTYPE= data set option. SAS does not have an equivalent data type for PERIOD. The value is represented in SAS as a character string. In this example, when the character string that represents the period ID is inserted into Teradata, it is implicitly converted to a PERIOD data type.

data x.mytest(DBTYPE=(validity='PERIOD(DATE) VALIDTIME'));
       i=1; 
       validity='(1973-02-03, 9999-12-31)';
       output;
run;

Reading in a PERIOD Data Type

A Teradata PERIOD data-type column can be read into SAS like any other column. It is represented in SAS as a character string, such as '(1973-02-03, 9999-12-31)'.

Temporal Qualifiers

Temporal tables contain rows that can be current, history, or future in the valid-time dimension. In the transaction-time dimension, rows can be open or closed. Temporal qualifiers specify what data is needed. As a LIBNAME and a data set option, TEMPORAL_QUALIFIER= lets you qualify queries in the valid-time or transaction-time dimension. For example, to fetch rows that are valid as of '2009-01-01' in a table, you must specify

TEMPORAL_QUALIFIER='VALIDTIME
                     AS OF DATE '2009-01-01' '

as a LIBNAME or a data set option when you query temporal tables.

The option that you specify for TEMPORAL_QUALIFIER= is free-form text. Here are some examples.

TEMPORAL_QUALIFIER='CURRENT VALIDTIME'
TEMPORAL_QUALIFIER='VALIDTIME AS OF DATE '2009-01-01' '
TEMPORAL_QUALIFIER='NONSEQUENCED VALIDTIME'
TEMPORAL_QUALIFIER='   SEQUENCED VALIDTIME'
TEMPORAL_QUALIFIER='NONSEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)''
TEMPORAL_QUALIFIER='   SEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)''
TEMPORAL_QUALIFIER='CURRENT TRANSACTIONTIME'
TEMPORAL_QUALIFIER='TRANSACTIONTIME AS OF TIMESTAMP '2009-01-01 01:02:03.123456' '

If you specify the temporal qualifier on the LIBNAME, it applies to the entire session because it is implemented by issuing session commands at connect time. For example, if you specify

TEMPORAL_QUALIFIER='ASOF PERIOD
                     '(1999-01-01, 2099-01-05)' '

on the LIBNAME, here is the Teradata SET SESSION command that is issued at connect time. The SQL is submitted as usual.

.SET SESSION ASOF PERIOD '(1999-01-01, 2099-01-05)'

If you submit the above command, the temporal qualifier is added as a prefix, as shown below.

ASOF PERIOD '(1999-01-01, 2099-01-05)'
SELECT * from TEMPORAL_TABLE;

   Sample code
   -----------

   /* PERIOD data types require the Teradata V13 server. */
   libname x teradata user=testuser pw=testpw server=td13;

   /* Create a table with the PERIOD(DATE) data type. 
      Note:  This is not a temporal table. */
   data x.mytest(DBTYPE=(validity='PERIOD(DATE)'));
   i=1; validity='(1973-02-03, 9999-12-31)'; output;
   run;

   /* Read from a table with a PERIOD data type? */
   proc print data=x.mytest;
   run;

   /* Use Fastload to load a table with a PERIOD data type. */
   proc datasets library=x;
     delete mytest;run;

   data x.mytest(DBTYPE=(validity='PERIOD(TIMESTAMP)') FASTLOAD=YES TPT=NO);
   i=1; validity='(1970-01-05 01:02:03.123, 1970-01-05 05:06:07.456)'; output;
   run;

   /* Tempral support starts in Teradata V13.10. */
   libname x teradata user=testuser pw=testpw server=td1310;

   /* Create a table with the PERIOD(DATE) data type. */
   data x.mytest(DBTYPE=(validity='PERIOD(DATE) VALIDTIME'));
   i=1; validity='(1973-02-03, 1999-12-31)'; output;
   i=2; validity='(2000-01-01, 2011-01-01)'; output;
   i=3; validity='(2011-01-02, 9999-12-31)'; output;
   run;

   /* Can we read a PERIOD data type?  
      You must select the row with i=2. */
   proc print data=x.mytest(TEMPORAL_QUALIFIER='CURRENT VALIDTIME');
   run;

   /* Consider data as of 1995-01-01. */
   libname x teradata user=testuser pw=testpw server=td1310
             TEMPORAL_QUALIFIER='VALIDTIME AS OF DATE '1995-01-01' '

   /* Row with i=1 is returned. */
   proc print data=x.mytest(DBSLICEPARM=ALL);
   run;