PERIOD(TIMESTAMP WITH TIME ZONE) Data Type | Teradata Vantage - PERIOD(TIMESTAMP WITH TIME ZONE) Data Type - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

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

Syntax

PERIOD(TIMESTAMP [ ( fractional_seconds_precision ) ] WITH TIME ZONE )
  [ attribute [...] ]
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 and Storage and Constraint Attributes for specific information.
The following data type attributes are supported for a PERIOD(TIMESTAMP WITH TIME ZONE) column:
  • NULL and NOT NULL
  • FORMAT 'format string'
  • TITLE
  • NAMED
  • DEFAULT NULL
  • DEFAULT value
  • WITH DEFAULT
For more information on these data type attributes, see Default Value Control Phrases and Data Type Formats and Format Phrases.
The following data type attributes are not supported for a PERIOD(TIMESTAMP 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

ANSI Compliance

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

Storage

A PERIOD(TIMESTAMP[(n)] WITH TIME ZONE) is a variable length data type and is saved as two TIMESTAMP WITH TIME ZONE 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) WITH TIME ZONE 24 if ending bound is not UNTIL_CHANGED 24 if ending bound is not UNTIL_CHANGED
13 if the ending bound is UNTIL_CHANGED 16 if the ending bound is UNTIL_CHANGED

External Representation of PERIOD(TIMESTAMP WITH TIME ZONE)

In field mode, Teradata Database returns PERIOD(TIMESTAMP 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.

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.

For modes other than field mode, and for input data, the external representation of PERIOD(TIMESTAMP WITH TIME ZONE) consists of 2-bytes representing the length of the data followed by 2 consecutive timestamp with time zone values. Each timestamp 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).
  • 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.
  • Time Zone Hour: 1 unsigned byte. This byte represents the time zone displacement in hours 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 time zone displacement in minutes.

Restrictions

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

Example: PERIOD(TIMESTAMP WITH TIME ZONE) Data Type

The following CREATE TABLE statement defines a PERIOD(TIMESTAMP(3) WITH TIME ZONE) 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 TIME ZONE) 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.