Specifies time-dimension criteria for retrieving data from Teradata.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| 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 |
Table of Contents
selects rows that are valid at the current time.
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.
selects history, current, or future rows that are valid for the specified time period.
treats the table as nontemporal.
selects rows that are open in transaction time.
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.
treats the table as nontemporal.
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.
/* Consider data as of 1995-01-01. */
libname x teradata user=myusr1 pw=mypwd1 server=mysrv1
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;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
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