TEMPORAL_QUALIFIER= Data Set Option

Specifies time-dimension criteria for retrieving data from Teradata.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	CURRENT VALIDTIME [valid-time column], CURRENT TRANSACTIONTIME [transaction-time column]
Interaction:	Specifying values in a DATA step overrides LIBNAME values.
Data source:	Teradata
See:	TEMPORAL_QUALIFIER= LIBNAME option

Syntax

Details

Examples

Example 1: Select Valid-Time Rows at the Current Time

Example 2: Select Valid-Time Rows for a Specific Date

Example 3: Select Transaction-Time Rows for a Specific Date and Time

Syntax

TEMPORAL_QUALIFIER=CURRENT VALIDTIME
| VALIDTIME AS OF PERIOD 'period' | SEQUENCED VALIDTIME
| NONSEQUENCED VALIDTIME 'period'

TEMPORAL_QUALIFIER=CURRENT TRANSACTIONTIME
| TRANSACTIONTIME AS OF PERIOD 'period'
| NONSEQUENCED TRANSACTIONTIME

Syntax Description

CURRENT VALIDTIME: selects rows that are valid at the current time.

VALIDTIME AS OF PERIOD 'period': selects rows with valid-time periods that overlap the specified AS OF period. For the period, you can specify either a single date or a time period (date range) by specifying a start date and an end date.

SEQUENCED VALIDTIME PERIOD 'period': selects history, current, or future rows that are valid for the specified time period.

NONSEQUENCED VALIDTIME PERIOD 'period': treats the table as nontemporal.

CURRENT TRANSACTIONTIME: selects rows that are open in transaction time.

TRANSACTIONTIME AS OF 'period': selects rows with transaction-time periods that overlap the specified AS OF period. For the period, you can specify either a single date or a time period (date range) by specifying a start date and an end date.

NONSEQUENCED TRANSACTIONTIME PERIOD 'period': treats the table as nontemporal.

Details

Use temporal qualifiers to specify time criteria for selecting data from temporal tables.

To use them, before the SQL add a value that you specify for one or more temporal qualifiers for a data set. For example, if you specify

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

in a DATA step,

'AS
                     OF PERIOD '(1999-01-01, 2099-01-05)' '

is added before the SQL to select the data.

Examples

Example 1: Select Valid-Time Rows at the Current Time

/* 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' '

/* ASOF PERIOD '(1999-01-01, 2099-01-05)' select * from mytest is submitted. */
proc print data=x.mytest(TEMPORAL_QUALIFIER='CURRENT VALIDTIME');
run;

Example 2: Select Valid-Time Rows for a Specific Date

This example extracts salary details for employees who worked on January 1, 2000.

Employment data from this employee table contains the ValidTime data column, JobDuration.

EName   E_Id    Dept    Job_duration
Sania   1001    Dept1   1990-01-01, 2003-01-01
Sania   1001    Dept3   2003-01-01, UNTIL_CHANGED
Ash     1002    Dept1   1995-01-01, 2000-01-01
Ash     1002    Dept2   1999-01-01, 2010-01-01

Salary data is from the ValidTime column, SalaryPeriod.

E_Id    Sal     SalaryPeriod
1001    10000   1990-01-01, 2003-01-01
1001    20000   2003-01-01, 2010-01-01
1001    30000   2010-01-01, UNTIL_CHANGED
1002    25000   1995-01-01, 2010-01-01

Here is the query.

VALIDTIME AS OF DATE’2000-01-01’
SELECT E.EName as Name, S.Sal as Salary
FROM Employee E, Salary S
WHERE E.E_Id = S.E_Id;

It produces this data as the result.

Name    Salary
Sania   10000
Ash     25000

Example 3: Select Transaction-Time Rows for a Specific Date and Time

This example extracts stock details as of a specific timestamp.

Data from this stock table contains a transaction-time dimension: the TransactionTime data column, RecordedTime.

StockName  StockValue  RecordedTime
Teradata   38          2006-01-01 10:00:00.000000+00:00, 
                       2006-01-01 12:10:10.000000+00:00
Teradata   37          2006-01-01 12:10:10.000000+00:00, 
                       2006-01-03 10:00:00.000000+00:00
Teradata   40          2006-01-03 10:00:00.000000+00:00, UNTIL_CLOSED

Here is the query.

TRANSACTIONTIME AS OF TIMESTAMP’2006-01-02 12:10:10.000000+00:00’
SELECT * FROM Stock;

It produces this data as the result.

StockName  StockValue
Teradata   37