Exploring Teradata 13's PERIOD Data Type

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.
Fan

Exploring Teradata 13's PERIOD Data Type

The PERIOD data type was introduced in Teradata 13.0. This first article will use examples to dig into the concepts that are part of this new feature. The second article will use examples to examine all the functions available in version 13.0.

Temporal: Where Do I Start?

Teradata's support for temporal data in version 13.10 is a very exciting development. If temporal is new to you, you may be wondering where to start. Well, a great place to start is with 13.0's PERIOD data type. Developing an understanding of this new data type will be a great foundation on which to build your understanding of temporal data.

This article does not explain Teradata's temporal data support in depth, but it also does not require any understanding of temporal data concepts. For temporal beginners, I suggest reading this first and then reading the temporal case study (pdf).

If you already understand temporal concepts, this example-driven article should help you gain a deeper understanding of Teradata's implementation of temporal concepts.

What is a Period?

A period is a span of time that has a beginning and an end. The end may be "forever".

Version 13.0 introduces Teradata's implementation of the PERIOD data type and supporting functions. A PERIOD column in Teradata can be any date or timestamp type. The begin and end have the same type.

Here are examples of PERIOD columns:

enrolled_period   PERIOD(DATE) FORMAT 'YYYY-MM-DD'  NULL
transaction_time PERIOD(TIMESTAMP) NOT NULL
work_period PERIOD(TIMESTAMP(3)) NOT NULL

Version 13.10 includes significantly more temporal functionality.

Closed/Open Period

The PERIOD data type uses a "closed/open" concept which simply means that the beginning of the period is included while the end of the period is excluded. Examples will help to explain the concept.

Closed/Open Period with Date

If we have a PERIOD with a begin date of 2010/01/01 and an end date of 2010/04/01, the period starts on January 1st and goes up to but not including April 1st. The period is January 1st through March 31st. April 1st is the first day that is past the period.

Closed/Open Period with Timestamp

If we have a PERIOD with a begin timestamp of 2010/01/01 08:00 and an end timestamp of 2010/04/01 08:10, the period starts on January 1st at 08:00 and goes up to but not including January 1st at 08:10. The period is 08:00 through 08:09. 08:10 is the first minute that is past the period. This will become more clear with more examples and by using the built-in functions.

Examples and Code

Enough text, let's get to some code.

Example Context

The "story" in the examples is that of an employer who offers health improvement programs on-line. The programs deal with health issues like weight management and tobacco usage.

INSERT

Here are two tables with PERIOD columns and the INSERT statements used to populate them.

Table with PERIOD(DATE)

CREATE MULTISET TABLE person_coaching_period (
person_id INTEGER NOT NULL,
coaching_program CHAR(2) NOT NULL,
enrolled_period PERIOD(DATE) FORMAT 'YYYY-MM-DD' NULL
)
PRIMARY INDEX (person_id);

This table stores data about an employee (person_id), a coaching program (SC: Smoking Cessation, WM: Weight Management), and the period when an employee is enrolled in a program.

Basic INSERT/SELECT Code

INSERT INTO person_coaching_period
VALUES (
1001,
'SC',
PERIOD(
DATE '2010-03-01',
DATE '2010-08-01'
)
);

SELECT * FROM person_coaching_period;

This date period translates to "2010-03-01 <= date < 2010-08-01".

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-08-01')

Table with PERIOD(TIMESTAMP)

CREATE MULTISET TABLE person_time_clock (
person_id INTEGER NOT NULL,
work_period PERIOD(TIMESTAMP) NOT NULL
)
PRIMARY INDEX (person_id);

This table stores data about an hourly employee (person_id) and their work time.

Basic INSERT/SELECT Code

INSERT INTO person_time_clock
VALUES (
1001,
PERIOD(
TIMESTAMP '2010-04-21 08:00:00',
TIMESTAMP '2010-04-21 17:00:00'
)
);

SELECT * FROM person_time_clock;

This date/time period translates to "2010-04-21 08:00:00 <= date/time < 2010-04-21 17:00:00".

person_id work_period
1001 ('2010-04-21 08:00:00.000000', '2010-04-21 17:00:00.000000')

UNTIL_CHANGED

To indicate that a PERIOD has no end date or date/time (also called "open"), use UNTIL_CHANGED.

INSERT INTO person_coaching_period
VALUES (
1001,
'WM',
PERIOD(
DATE '2010-08-01',
UNTIL_CHANGED
)
);

INSERT INTO person_coaching_period
VALUES (
1003,
'WM',
PERIOD(
CURRENT_DATE,
UNTIL_CHANGED
)
);

INSERT INTO person_time_clock
VALUES (
1002,
PERIOD(
CURRENT_TIMESTAMP,
UNTIL_CHANGED
)
);

SELECT * FROM person_coaching_period;
SELECT * FROM person_time_clock;

Results

person_id coaching_program enrolled_period
1001 WM ('2010-08-01', '9999-12-31')
1003 WM ('2010-10-22', '9999-12-31')
person_id work_period
1002 ('2010-10-22 09:08:21.160000', '9999-12-31 23:59:59.999999')

UPDATE

First, the simplest UPDATE for period is to overwrite both dates. That can be done with an explicit UPDATE ... PERIOD statement like this:

UPDATE person_coaching_period
SET enrolled_period = PERIOD(DATE '2010-08-01', DATE '2010-09-01')
WHERE ...

The more interesting case is when you want to update just a portion of the PERIOD. In this example, we want to close a set of records without affecting the records' begin dates. This will use the BEGIN function.

Starting with these records:

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-08-01')
1003 WM ('2010-03-15', '9999-12-31')
1005 SC ('2010-03-01', '9999-12-31')
1006 SC ('2010-04-01', '9999-12-31')

we execute this UPDATE statement to "close" some of the records:

UPDATE person_coaching_period
SET enrolled_period = PERIOD(BEGIN(enrolled_period), DATE '2010-09-01')
WHERE coaching_program = 'SC';

Results

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-09-01')
1003 WM ('2010-03-15', '9999-12-31')
1005 SC ('2010-03-01', '2010-09-01')
1006 SC ('2010-04-01', '2010-09-01')

Note that none of the begin dates was modified.

IS UNTIL_CHANGED

There is a problem with the UPDATE statement above. The goal was to "close a set of records", but the UPDATE statement changed the end date of the first record which was not "open".

Starting again with the same set of beginning records, let's do it right this time by using the "IS UNTIL_CHANGED" qualifier on the end date to update only the open records. This will use the END function.

UPDATE person_coaching_period
SET enrolled_period = PERIOD(BEGIN(enrolled_period), DATE '2010-09-01')
WHERE coaching_program = 'SC'
AND END(enrolled_period) IS UNTIL_CHANGED;

Here are the new results:

person_id coaching_program enrolled_period
1001 SC ('2010-03-01', '2010-08-01')
1003 WM ('2010-04-01', '9999-12-31')
1005 SC ('2010-03-01', '2010-09-01')
1006 SC ('2010-04-01', '2010-09-01')

Now the first record has retained its original end date.

"IS UNTIL_CHANGED" also works for other statements like SELECT. "IS NOT UNTIL_CHANGED" also works as expected. More examples are in the attached scripts.

A Glimpse at the Power of PERIOD

This example jumps ahead to give a glimpse of the power available in 13.0's PERIOD data type and associated functions.

The example uses a new table (person_login) that includes just a normal TIMESTAMP. This table records the date/time when an employee (person_id) logs into an on-line system.

CREATE MULTISET TABLE person_login (
person_id INTEGER NOT NULL,
login_dtm TIMESTAMP NOT NULL
)
PRIMARY INDEX (person_id);

First, we load test data into the person_employment_period and person_login tables.

INSERT INTO person_employment_period    VALUES (1001, PERIOD(DATE '2008-04-01', UNTIL_CHANGED));
INSERT INTO person_login VALUES (1001, TIMESTAMP '2008-04-10 09:00:00');
INSERT INTO person_login VALUES (1001, TIMESTAMP '2008-04-21 11:11:11');
INSERT INTO person_login VALUES (1001, TIMESTAMP '2008-10-01 22:22:22');

INSERT INTO person_employment_period VALUES (1002, PERIOD(DATE '2009-12-01', UNTIL_CHANGED));
INSERT INTO person_login VALUES (1002, TIMESTAMP '2010-01-01 09:00:00');
INSERT INTO person_login VALUES (1002, TIMESTAMP '2010-01-21 09:00:00');

The question is this: find the delay between starting employment and first login. This example query uses multiple PERIOD-related functions, but everything here is in 13.0. The example illustrates the use of INTERVAL with PERIOD and TIMESTAMP.

SELECT
pep.person_id,
CAST(BEGIN(pep.employment_period) AS TIMESTAMP) AS BeginEmployment,
pl.login_dtm,
INTERVAL(
PERIOD(
CAST(BEGIN(pep.employment_period) AS TIMESTAMP),
pl.login_dtm
)
) HOUR(4) AS HoursBeforeFirstLogin
FROM person_employment_period AS pep
JOIN person_login AS pl
ON pep.person_id = pl.person_id
QUALIFY RANK() OVER (
PARTITION BY pl.person_id
ORDER BY pl.login_dtm
) = 1
ORDER BY pep.person_id;

Results

(Zeros have been removed for readability.)

person_id BeginEmployment login_dtm HoursBeforeFirstLogin
1001 4/1/2008 4/10/2008 09:00 225
1002 12/1/2009 1/1/2010 09:00 753

Conclusion

The PERIOD data type introduced in Teradata 13.0 is very powerful in itself. Version 13.10 introduces significant capabilities on top of what is in 13.0, but understanding 13.0's features will give you an important basis for moving on to 13.10.

Continue Exploring!

Download the attachment to this article and try it out. If you come up with good examples that help explain the concepts, please share them in the comments.

Upcoming: Exploring Teradata 13's PERIOD Functions

Watch for the second article that will use examples to explore all of the PERIOD-related functions available in Teradata 13.0 (published here on 2010-02-15).

References

    • Teradata Temporal at teradata.com: find these pdf files linked on the right side:
        • A Case Study of Temporal Data (excellent reading)
        • Teradata Database Temporal Option
9 REPLIES

Re: Exploring Teradata 13's PERIOD Data Type

Great Job

Re: Exploring Teradata 13's PERIOD Data Type

Good post with great examples.

Re: Exploring Teradata 13's PERIOD Data Type

How does the Teradata Warehouse Miner (and the ADS Generator) support this new data type in the various algorithms offered?

Is there 'native' support or is it via a CAST?

Teradata Employee

Re: Exploring Teradata 13's PERIOD Data Type

Great article and thank you for the examples!

Teradata Employee

Re: Exploring Teradata 13's PERIOD Data Type

This got me through a couple of issues. The period is also the ValidDate for the table.

But how do I update a period from another table?

Enthusiast

Re: Exploring Teradata 13's PERIOD Data Type

This is very good article...I am bit clear now.

Enthusiast

Re: Exploring Teradata 13's PERIOD Data Type

Yes, its very good.

Teradata Employee

Re: Exploring Teradata 13's PERIOD Data Type

well written. Thks!

Teradata Employee

Re: Exploring Teradata 13's PERIOD Data Type

Excellent , Thanks so much.