15.10 - PERIOD(TIME WITH TIME ZONE) Data Type - Teradata Database

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(TIME[(n)] WITH TIME ZONE) column records the beginning and ending bounds in UTC form in the same manner as exists currently for a TIME WITH TIME ZONE column.

 

where:

Syntax element …	Specifies …
fractional_seconds_precision	a single digit representing the number of significant digits in the fractional portion of the SECOND field. Values for fractional_seconds_precision range from 0 to 6 inclusive. The default precision is 6.
attributes	appropriate data type, column storage, or column constraint attributes. See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information. The following data type attributes are supported for a PERIOD(TIME WITH TIME ZONE) column: NULL and NOT NULL FORMAT 'format string' TITLE NAMED DEFAULT NULL DEFAULT value For details on these data type attributes, see Chapter 11: “Default Value Control Phrases” and Chapter 12: “Data Type Formats and Format Phrases.” The following data type attributes are not supported for a PERIOD(TIME WITH TIME ZONE) column: DEFAULT USER DEFAULT DATE DEFAULT TIME DEFAULT CURRENT_DATE DEFAULT CURRENT_TIME[(n)] DEFAULT CURRENT_TIMESTAMP[(n)] UPPERCASE or UC CASESPECIFIC or CS CHARACTER SET WITH DEFAULT

Period types are a Teradata extension to the ANSI SQL:2011 standard.

A PERIOD(TIME[(n)] WITH TIME ZONE) is a fixed length data type and is saved as two TIME WITH TIME ZONE values.

Element Type	Field Size in Bytes	Maximum Size in Bytes in the Row
TIME(n) WITH TIME ZONE	16	16

In field mode, Teradata Database returns PERIOD(TIME WITH TIME ZONE) 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.For modes other than field mode, and for input data, the external representation of PERIOD(TIME WITH TIME ZONE) consists of two consecutive time with time zone values. Each time value consists of multiple fields as explained below and returned in the specified order:

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

Hour: 1 unsigned byte. This byte represents the number of hours.

Minute: 1 unsigned byte. This byte represents the number of minutes.

Time Zone Hour: 1 unsigned byte. This byte represents the hours portion of the time zone displacement along with whether the displacement is + or -. A value of 16 represents 0 hours. A value less than 16 represents a negative time zone displacement for the specified hours; that is, if this is 10, the time zone is displaced by -10 hours. If greater than 16, it specifies a positive time zone displacement of (Time Zone Hour - 16) hours; that is, a value of 20 implies a +4 hour displacement.

Time Zone Minute: 1 unsigned byte. This byte represents the minute's portion of the time zone displacement.

UDFs or external stored procedures that are written in Java do not support arguments or return values that have a Period data type.

A primary index column or partitioning column cannot be a column that has a Period data type.

The following CREATE TABLE statement defines a PERIOD(TIME(6) WITH TIME ZONE) column since the precision defaults to 6.

CREATE TABLE t8

(

      job_id        INTEGER,

      job_desc      CHARACTER(15),

      job_status    CHARACTER(1),

      job_hours     PERIOD(TIME WITH TIME ZONE));

For information on functions and operators that apply to Period types, see SQL Functions, Operators, Expressions, and Predicates.