Period Data Type and Derived Period Column Scenario

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Period Data Type and Derived Period Column Scenario



Overview: Period Data Types and Derived Period Columns

Teradata Database supports storage and manipulations that involve durations, spans of time defined by a beginning and ending bounds:

  • Period data types allow creation of single values to represent durations, where the beginning and ending bounds can be any pairs of standard Teradata Database DateTime values (DATE, TIME, and TIMESTAMP data types).
  • Derived period columns allow you to define a duration using values from two existing Date or TIMESTAMP columns.

Both Period data types and derived period columns define durations that are inclusive of the beginning duration bound, and exclusive of the ending bound.

Teradata Database provides a rich variety of functions, operators, and predicates that can be used on Period data type and derived period columns.





Period Data Types

This feature was introduced in Teradata Database 13.10.



Description

Period data types represent durations. A Period data type value includes the beginning and ending bound that defines the duration. Teradata Database supports the following Period data types, where the differences are based on the type of DateTime data used to define the duration bounds:
  • PERIOD(DATE)
  • PERIOD(TIME[(n)])
  • PERIOD(TIME[(n)] WITH TIME ZONE)
  • PERIOD(TIMESTAMP[(n)])
  • PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)


More...


Benefits

  • Extends the temporal capabilities of Teradata Database with a data type that represents a duration.
  • Period data type columns are defined using standard SQL CREATE TABLE statements, and can be added to or dropped from tables using standard ALTER TABLE statements.
  • Teradata Database provides a rich variety of functions, operators, and predicates that can be used on PERIOD data.
  • Allows faster development of efficient applications that use and manipulate durations.
  • Period data types can be used to define Teradata temporal tables.


Considerations

  • Constraint definitions cannot be specified for Period data type columns.
  • Column storage attributes cannot be specified for Period data type columns.
  • Distinct UDTs cannot be created as Period data types.
  • Window functions are not allowed on Period data types.
  • Indexes cannot be specified for Period data type columns.




Derived Period Column


This feature was introduced in Teradata Database 14.10.



Description

A derived period column provides a way to represent a span of time in a single table column. The derived period column combines a pair of defined DateTime type columns that specify the beginning and ending bounds of a duration. Under most circumstances, derived period columns are treated by Teradata Database similarly to columns defined to have true Period data types.



Benefits

  • Derived period columns are defined using standard SQL CREATE TABLE statements, and can be added to or dropped from tables using standard ALTER TABLE statements.
  • Derived period columns can be used with all functions, operators, and predicates that support true Period data types.
  • The EXPAND clause of SELECT statements supports expansion on derived period columns.
  • Derived period columns can be used to define temporal tables.
  • Provides Teradata Database compatibility with existing temporal applications that use two columns to define durations.
  • The two component columns of a derived period column can be used anywhere a regular DateTime column can be used, for example in index definitions, partition expressions, projections, and check constraints.
  • A check constraint that ensures the beginning bound column value is a DateTime value prior to the corresponding ending bound column value is automatically enforced for all DML operations on the component columns.
  • Any constraint definition or function allowed on a DateTime column is allowed on the component begin and end columns of a derived period column. Such functions include hash index, unique or primary key constraints, check constraints, reference constraints.
  • Statistics can be collected on begin and end bound columns that define a derived period column, which can improve query performance. Consequently, temporal tables that use derived period columns rather than Period data type columns for their VALIDTIME and TRANSACTIONTIME columns can display better performance.
  • Although derived period columns share the restrictions of true Period data types, the component begin and end bound columns that comprise a derived period column are regular DateTime columns, and can be used and manipulated, for the most part, as any regular DateTime column.


Considerations

  • Derived period columns cannot be:
    • part of primary or secondary index
    • included in a partition expression
    • named in a SELECT list
  • Derived period columns must always be defined as NOT NULL.
  • The component begin and end bound columns that define a derived period column cannot have a TIME data type.
  • The only column attributes that can be defined for a derived period column are AS VALIDTIME and AS TRANSACTIONTIME, used to create temporal tables.
  • Each derived period column defined for a table counts against the system limit for columns per table.





Differences Between Period Data Type Columns and Derived Period Columns


Period data type columns and derived period columns are largely analogous, and can be used for similar purposes. However, there are some differences that are noteworthy. In most cases, restrictions on Period data type columns also apply to derived period columns, however, the component columns that comprise derived period columns are generally not so restricted.

Period Data Type Columns Derived Period Columns
Period data type columns support many column attributes. These include data type attributes (such as NULL and NOT NULL, FORMAT 'format string', TITLE, DEFAULT value), column constraint attributes (such as UNIQUE and CHECK), and column storage attributes (such as COMPRESS). Derived period columns support only the VALIDTIME and TRANSACTIONTIME data type attributes if the derived period column is part of a temporal table.
Statistics cannot be collected on Period data type columns. Statistics cannot be collected on derived period columns, however, statistics can be collected on the component columns that function as the begin and end bounds of a derived period column.
Constraint definitions cannot be specified for Period data type columns. Constraint definitions can be specified on the component columns of a derived period column.
Period data type columns cannot be part of secondary indexes. The component columns of a derived period column can be part of secondary indexes.
Period data type columns can be defined to allow NULL values. Component begin and end bound DateTime columns of a derived period column must be defined as NOT NULL.
Period data type column data can use TIME data types in the PERIOD data type constructors unless the period data type column is a VALIDTIME or TRANSACTIONTIME column in a temporal table. Component begin and end bound DateTime columns of a derived period column cannot be of type TIME.
Period data type columns can be included in column specifications for SELECT statements (can be projected in SELECT lists). Derived period columns cannot be included in SELECT lists, however, component columns of a derived period column can be in SELECT lists.
Begin and end bound values of Period data type columns can be used in partition expressions. Derived period columns cannot be part of a partition expression, however their component begin and end time bound columns can be used in partition expressions.





Scenario: Determining Length of Service for Employees


This scenario creates two versions of an employee data table. One table uses a period data type column to contain the start and end employment date for each employee. The second table uses a derived period column, and two columns that define the start and end employment date for each employee. These tables allows for querying the duration of service for any employee. The scenario also demonstrates inserting employee records having period data into the table, and updates the start date for one employee.


For More Information about Period Data Types and Derived Period Columns

For more information about Period data types and derived period columns, and about the SQL used in these examples, see:

Document Description
SQL Data Types and Literals, B035-1143 Describes the Period data types.
SQL Data Definition Language - Syntax and Examples, B035-1144 Describes defining, adding, and removing Period data type columns and derived period columns.
SQL Functions, Operators, Expressions, and Predicates, B035-1145 Describes functions, operators, and predicates that are used with Period data types and on derived period columns.
SQL Data Manipulation Language, B035-1146 Provides examples of DML operations on Period data types and derived period columns.






Example: Create a Table with a PERIOD(DATE) Data Type Column


The following CREATE TABLE statement defines an employee table that includes a PERIOD(DATE) data type column with a default value set using a Period literal.

CREATE TABLE Service_Period
(
employee_id INTEGER,
employee_name CHARACTER(15),
employee_duration PERIOD(DATE)
DEFAULT PERIOD '(2005-02-03, 2006-02-03)'
);





Example: Create a Table with a Derived Period Column


The following CREATE TABLE statement defines an employee table that includes a derived period column using DATE type values to define the beginning and ending bounds of the derived period.

CREATE TABLE Service_Derived_Period
(
employee_id INTEGER,
employee_name CHARACTER(15),
employee_beg DATE NOT NULL FORMAT 'YYYY-MM-DD',
employee_end DATE NOT NULL FORMAT 'YYYY-MM-DD',
PERIOD FOR employee_duration(employee_beg, employee_end)
);





Example: Insert Into a Table with a Period Data Type Column


The following INSERT statements insert employee data into a PERIOD(DATE) column.

INSERT INTO Service_Period VALUES (1,'John',PERIOD(DATE '2005-02-03',DATE '2013-02-04'))
;INSERT INTO Service_Period VALUES (2,'Mary',PERIOD(DATE '2000-12-15',DATE '2013-02-04'))
;INSERT INTO Service_Period VALUES (3,'Adam',PERIOD(DATE '2001-06-30',DATE '2013-02-04'))
;INSERT INTO Service_Period VALUES (4,'Simon',PERIOD(DATE '2002-03-15',DATE '2013-02-04'));





Example: Insert Into a Table With a Derived Period Column


The following INSERT statements insert employee data into the DATE type columns that are used to define the bounds of a derived period column.

INSERT INTO Service_Derived_Period VALUES (1,'John',(DATE '2005-02-03'),(DATE '2013-02-04'))
;INSERT INTO Service_Derived_Period VALUES (2,'Mary',(DATE '2000-12-15'),(DATE '2013-02-04'))
;INSERT INTO Service_Derived_Period VALUES (3,'Adam',(DATE '2001-06-30'),(DATE '2013-02-04'))
;INSERT INTO Service_Derived_Period VALUES (4,'Simon',(DATE '2002-03-15'),(DATE '2013-02-04'));





Example: Select an Interval From Period Data


This SELECT statement returns the number of years of service for all employees using Period data.

SELECT employee_name, INTERVAL (employee_duration) YEAR FROM Service_Period
ORDER BY 2 DESC;

Result:

employee_name    INTERVAL(employee_duration) YEAR
--------------- --------------------------------
Mary 13
Adam 12
Simon 11
John 8





Example: Select an Interval From Derived Period Data


This SELECT statement returns the number of years of service for all employees using a derived period column.

SELECT employee_name, INTERVAL (employee_duration) YEAR FROM Service_Derived_Period 
ORDER BY 2 DESC;

Result:

employee_name    INTERVAL(employee_duration) YEAR
--------------- --------------------------------
Mary 13
Adam 12
Simon 11
John 8





Example: Update Period Data


This UPDATE statement changes the start date for John in the Period data type column.

UPDATE Service_Period SET employee_duration = PERIOD(DATE '2006-02-01',DATE '2013-02-04')
WHERE employee_id = 1;

This SELECT statement returns the number of years of service for John from the Period data type column.

SELECT employee_name, INTERVAL (employee_duration) YEAR
FROM Service_Period
WHERE employee_id = 1;

Result:

employee_name    INTERVAL(employee_duration) YEAR
--------------- --------------------------------
John 7





Example: Update Derived Period Column


This UPDATE statement changes the start date for John, which defines the start bound of the employee_duration derived period column.

UPDATE Service_Derived_Period SET employee_beg = (DATE '2006-02-01')
WHERE employee_id = 1;

This SELECT statement queries the derived period column to return the number of years of service for John.

SELECT employee_name, INTERVAL (employee_duration) YEAR 
FROM Service_Derived_Period
WHERE employee_id = 1;

Result:

employee_name    INTERVAL(employee_duration) YEAR
--------------- --------------------------------
John 7



<SCRIPT type="text/javascript"><BR /><BR /> jQuery(function() {<BR /> jQuery("span.notetitle").css({"font-weight":"bold"});<BR /> jQuery("div.note").css({"border":"1px solid grey"});<BR /> jQuery("pre").css({"background-color":"#EEEEEE", "border":"1px solid black"});<BR /> jQuery(".toggle, .toggle-02, .toggle-03, .toggle-04, .toggle-05, .toggle-06, .toggle-07, .toggle-08, .toggle-09, .toggle-10, .toggle-11, .toggle-12, .toggle-13").css({"cursor":"pointer"});<BR /> jQuery(".expandme").css({"color":"#0E45FF", <BR /> "text-decoration":"underline", <BR /> "margin-left":"2em"});<BR /> jQuery(".toggle-02, .toggle-03, .toggle-04, .toggle-05, .toggle-06, .toggle-07, .toggle-08, .toggle-09, .toggle-10, .toggle-11, .toggle-12, .toggle-13").css( <BR /> {"background-image":"url('https://developer.teradata.com/sites/all/files/plus_arrow.gif')", <BR /> "background-repeat":"no-repeat", <BR /> "background-position":"5px 7px",<BR /> "text-indent":"2em",<BR /> "display":"block"});<BR /> jQuery(".panel, .panel-02, .panel-03, .panel-04, .panel-05, .panel-06, .panel-07, .panel-08, .panel-09, .panel-10, .panel-11, .panel-12, .panel-13").css({"display":"none"});<BR /> jQuery("[class^='toggle-']").append("<div style='color:#0E45FF; text-decoration:underline;'>More...</div>");<BR /> });<BR /> jQuery(function() {<BR /> jQuery(".toggle").click(function(){<BR /> jQuery(".panel").slideToggle("fast"); <BR /> });<BR /> jQuery(".toggle-02").click(function(){<BR /> jQuery(".panel-02").slideToggle("fast", function() {<BR /> jQuery(".toggle-02").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-03").click(function(){<BR /> jQuery(".panel-03").slideToggle("fast", function() {<BR /> jQuery(".toggle-03").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-04").click(function(){<BR /> jQuery(".panel-04").slideToggle("fast", function() {<BR /> jQuery(".toggle-04").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-05").click(function(){<BR /> jQuery(".panel-05").slideToggle("fast", function() {<BR /> jQuery(".toggle-05").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-06").click(function(){<BR /> jQuery(".panel-06").slideToggle("fast", function() {<BR /> jQuery(".toggle-06").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-07").click(function(){<BR /> jQuery(".panel-07").slideToggle("fast", function() {<BR /> jQuery(".toggle-07").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-08").click(function(){<BR /> jQuery(".panel-08").slideToggle("fast", function() {<BR /> jQuery(".toggle-08").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-09").click(function(){<BR /> jQuery(".panel-09").slideToggle("fast", function() {<BR /> jQuery(".toggle-09").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-10").click(function(){<BR /> jQuery(".panel-10").slideToggle("fast", function() {<BR /> jQuery(".toggle-10").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-11").click(function(){<BR /> jQuery(".panel-11").slideToggle("fast", function() {<BR /> jQuery(".toggle-11").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> }); <BR /> jQuery(".toggle-12").click(function(){<BR /> jQuery(".panel-12").slideToggle("fast", function() {<BR /> jQuery(".toggle-12").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> jQuery(".toggle-13").click(function(){<BR /> jQuery(".panel-13").slideToggle("fast", function() {<BR /> jQuery(".toggle-13").css({"background-image":"url('https://developer.teradata.com/sites/all/files/minus_arrow.gif')"});<BR /> }); <BR /> });<BR /> });<BR /></SCRIPT>