15.10 - PERIOD(TIMESTAMP) Data Type - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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.

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(TIMESTAMP) column:

  • NULL and NOT NULL
  • FORMAT 'format string'
  • TITLE
  • NAMED
  • DEFAULT NULL
  • DEFAULT value
  • WITH DEFAULT
  • 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(TIMESTAMP) 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
  • Period types are a Teradata extension to the ANSI SQL:2011 standard.

    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

    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.

    For modes other than field mode, and for input data, the external representation of PERIOD(TIMESTAMP) consists of 2-bytes representing the length of the data followed by 2 consecutive timestamp 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.
  • 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(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
    );

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