What’s a Period Data Type and Why do I Care?

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

What’s a Period Data Type and Why do I Care?

More than likely, you need to keep track of the beginning and ending of some kind of an event. Maybe you track how long employees have been with your company, how much time users spend on a particular web site, or the duration of insurance policies.

In other words, you need to track temporal data. And you probably use a couple of DATE or TIME or TIMESTAMP columns to do just that.

Consider the following employee table that tracks the job duration of employees by using two DATE columns: start_date and end_date.

CREATE SET TABLE employee (
employee_number INTEGER NOT NULL,
manager_employee_number INTEGER,
department_number INTEGER,
job_code INTEGER,
last_name CHAR(20) NOT NULL,
first_name VARCHAR(30) NOT NULL,
salary_amount DECIMAL(10,2) NOT NULL,
birthdate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
start_date DATE FORMAT 'YYYY-MM-DD' NOT NULL,
end_date DATE FORMAT 'YYYY-MM-DD' NOT NULL)
PRIMARY INDEX ( employee_number );

What if you could represent temporal data using a single column? What if you could use operators, functions, and predicates to manipulate your temporal data?

Behold the Period data type, available in Release 13.0 and the first step toward extending the temporal capabilities of Teradata Database.

Here is the employee table, redefined to track the job duration of employees using a Period column instead of two DATE columns.

CREATE SET TABLE employee (
employee_number INTEGER NOT NULL,
manager_employee_number INTEGER,
department_number INTEGER,
job_code INTEGER,
last_name CHAR(20) NOT NULL,
first_name VARCHAR(30) NOT NULL,
salary_amount DECIMAL(10,2) NOT NULL,
birthdate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
job_duration PERIOD(DATE) FORMAT 'YYYY-MM-DD' NOT NULL)
PRIMARY INDEX ( employee_number );

The Period Data Type

A period is an anchored duration that represents a set of contiguous time granules within the duration. It has a beginning bound (defined by the value of a beginning element) and an ending bound (defined by the value of an ending element). Beginning and ending elements can be DATE, TIME, or TIMESTAMP types, but both must be the same type.

The duration that a period represents starts from the beginning bound and extends up to, but does not include, the ending bound.

If the element type is DATE or TIMESTAMP, the ending bound can have a special value of UNTIL_CHANGED, where Teradata Database interprets the ending bound of the period as forever, or without end.

To insert or update a period value, you can use the PERIOD constructor. Here is an example that uses the PERIOD constructor to insert a value into the employee table:

INSERT INTO employee
VALUES (400126, 210219, 589471, 882800, 'Lee', 'Gloria', 48000, DATE '1980-03-17', PERIOD (DATE '2008-11-29', UNTIL_CHANGED));

Manipulating Period Data

Just what can you do with Period data? Sure, you can use existing assignment and comparison operators. But you also have many other operators, functions, and predicates to manipulate your period data.

Suppose you want a list of employees who were working at your company anytime between May 1, 2006 and September 24, 2007 (including May 1, 2006 but not including September 24, 2007). The OVERLAPS function operates on period types, making such a query as easy as this:

SELECT first_name, last_name
FROM employee
WHERE job_duration OVERLAPS
PERIOD(DATE '2006-05-01', DATE '2007-09-24');

Now suppose you want to further narrow your list to those employees who were also working at your company for at least three months before May 1, 2006. Here’s a way to do that:

SELECT first_name, last_name
FROM employee
WHERE INTERVAL(job_duration LDIFF PERIOD(DATE '2006-05-01', DATE '2007-09-24')) MONTH > 3;

What the LDIFF operator does is return the portion of the first period that exists before the beginning of the second period for periods that overlap. Passing that value into the INTERVAL function produces the duration of that value, in this case in terms of months.

Hopefully, you’re catching on that the Period type, along with the operators, functions, and predicates that support it, can really simplify how you represent and manipulate your temporal data.

Additional Information on Period Types

The following documentation, available from http://www.info.teradata.com, provides more information about Period types: 

  • SQL Data Types and Literals, B035-1143-098A
  • SQL Functions, Operators, Expressions, and Predicates, B035-1145-098A

     
2 REPLIES
Enthusiast

Re: What’s a Period Data Type and Why do I Care?

LDIFF returns the portion of the first period that exists before the begin of the second period of periods overlaps... this is is a quite awkward way to describe the function . Does this mean that LDFIF takes two PERIOD parameters? ie..
LDIFF ( P1, P2), ie P1 LDIFF P2 Where P1 = (S1,E1), P2 = (S2,E2) the result of LDIFF is :

if S2 > E2 then LDIFF(P1,P2) = null / no overlap
if S1 < S2 < E1 then LDIFF(P1,P2) = PERIOD(S1,S2)
if S2 < S1 then LDIFF(P1,P2 ) = P1 exits after S1...

Please advise. Thanks!
Enthusiast

Re: What’s a Period Data Type and Why do I Care?

Hello emilwu,
That's how I read it, except the third one (period 2 starts before period 1) the result is NULL. See Function ref: "BEGIN(p1) is not less than BEGIN(p2), the result is NULL."

This is a great addition to the Teradata platform, particularly when dealing with modelled history tables and doing a "as at" type of query.

I have a question relating to performance and tuning. Having had to query your typical history table with start and dates, how do period datatypes compare in terms of performance. Can they be optimised, although I see that they can't be in a PI or SI. Thanks.