A data type that has two DateTime elements associated with it.
| The DateTime element... | Specifies... |
|---|---|
| beginning | the beginning bound of a period. |
| ending | the ending bound of a period. |
The beginning bound is inclusive, and the ending bound is exclusive; that is, the DateTime range starts at the beginning bound and extends up to but not including the ending bound.
A PERIOD(TIMESTAMP[(n)]) column records the beginning and ending bounds in UTC form in the same manner as exists currently for a TIME or TIMESTAMP column.
Syntax
PERIOD(TIMESTAMP [ ( fractional_seconds_precision ) ] ) [ attribute [...] ]
- fractional_seconds_precision
- A single digit representing the number of significant digits in the fractional portion of the SECOND field.
- attributes
- Appropriate data type, column storage, or column constraint attributes. See Core Data Type Attributes and Storage and Constraint Attributes for specific information.
ANSI Compliance
Period types are a Teradata extension to the ANSI SQL:2011 standard.
Storage
A PERIOD(TIMESTAMP[(n)]) is a variable length data type and is saved as two TIMESTAMP values. The ending bound value of UNTIL_CHANGED occupies a single byte for these variable length data types.
| Element Type | Field Size in bytes | Maximum Size in bytes in the row |
|---|---|---|
| TIMESTAMP(n) | 20 if ending bound is not UNTIL_CHANGED | 24 if ending bound is not UNTIL_CHANGED |
| 11 if the ending bound is UNTIL_CHANGED | 16 if the ending bound is UNTIL_CHANGED |
External Representation of PERIOD(TIMESTAMP)
In field mode, Teradata Database returns PERIOD(TIMESTAMP) data as character data.
Assume L is the maximum length of the formatted character string for the format associated with this Period data type. The resulting character string contains two strings representing the beginning and ending bounds of the period value expression, each up to length L , and each enclosed in apostrophes ('), separated by comma and a space (,), and then enclosed within a left and right parenthesis [( )]. Thus, the maximum length of the resulting character string is 2* L +8.
Assume the actual length is K which may be less than 2*L+8, for example, if the format includes the full names of months and the specific month for a bound is July.
- Second: 4-byte, signed integer flipped to client form. This integer represents the number of seconds as a scaled decimal (for example, 12.56 seconds is returned as 12560000).
- Year: 2-byte, signed short integer flipped to client form. This integer represents the year value.
- Month: 1 unsigned byte. This byte represents the month value.
- Day: 1 unsigned byte. This byte represents the day of the month.
- Hour: 1 unsigned byte. This byte represents the number of hours.
- Minute: 1 unsigned byte. This byte represents the number of minutes.
Restrictions
A primary index column or partitioning column cannot be a column that has a Period data type.
Example: PERIOD(TIMESTAMP) Data Type
The following CREATE TABLE statement defines a PERIOD(TIMESTAMP(3)) column with a default value specified using WITH DEFAULT. The WITH DEFAULT option sets the default value to a Period value constructor with the beginning argument set to CURRENT_TIMESTAMP(3) and the ending argument set to UNTIL_CHANGED.
CREATE TABLE t4 ( employee_id INTEGER, employee_name CHARACTER(15), employee_duration PERIOD(TIMESTAMP(3)) WITH DEFAULT );
Related Topics
For information on functions and operators that apply to Period types, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.