Period Literals | Data Types and Literals | Teradata Vantage - Period Literals - Advanced SQL Engine

Period Literals | Data Types and Literals | Teradata Vantage - Period Literals - 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

Product Category

Teradata Vantage™

Specifies a constant value of a Period data type.

Both temporal and nontemporal tables support Period data types and literals. However, Period data types and literals are not ANSI standard SQL; therefore, you can use them to define Teradata temporal tables, but not ANSI temporal tables. For more information about Teradata temporal tables, see Teradata Vantage™ - Temporal Table Support , B035-1182 . For more information about ANSI temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 .

Syntax

PERIOD '(
  beginning_bound { - | , }
  { ending_bound | UNTIL_CHANGED | UNTIL_CLOSED }
)'

beginning_bound, ending_bound

Date, time, or timestamp values in the same format as Date, Time, or Timestamp literals.

A comma (,) separator between beginning_bound and ending_bound can include optional spaces on either side. A hyphen (-) separator must have a space before and after.

Date values have the following format:

YYYY-MM-DD

Time values have the following formats:

hh:mi:ss
hh:mi:sssignhh:mi
hh:mi:ss.ssssss
hh:mi:ss.sssssssignhh:mi

Timestamp values have the following formats:

YYYY-MM-DD hh:mi:ss
YYYY-MM-DD hh:mi:sssignhh:mi
YYYY-MM-DD hh:mi:ss.ssssss
YYYY-MM-DD hh:mi:ss.sssssssignhh:mi

For more information, see Date Literals, Time Literals, and Timestamp Literals.

UNTIL_CHANGED

The ending bound has a value of forever, or until it is changed. UNTIL_CHANGED may only be specified when beginning_bound is a Date or Timestamp value.

If beginning_boundis a date value, UNTIL_CHANGED specifies a value of DATE '9999-12-31'. If beginning_bound is a timestamp value, UNTIL_CHANGED specifies a value of TIMESTAMP '9999-12-31 23:59:59.999999+00:00', with precision truncated to the precision of beginning_bound and the time zone omitted if beginning_bound has no time zone.

UNTIL_CLOSED

An ending bound for the Period value of a transaction-time column of a temporal table that indicates that the row is an open row. UNTIL_CLOSED has a value of TIMESTAMP '9999-12-31 23:59:59:999999+00:00'.

For more information about Teradata temporal tables, see Teradata Vantage™ - Temporal Table Support , B035-1182 .

Data Types

IF the format of the Period literal is …	THEN the data type is …
hh:mi:ss	PERIOD(TIME(0)).
hh:mi:sssignhh:mi	PERIOD(TIME(0) WITH TIME ZONE).
hh:mi:ss.ssssss	PERIOD(TIME(n)), where n is the maximum number of fractional seconds digits in the beginning and ending bound values, or, if the ending bound value is UNTIL_CHANGED, the number of fractional seconds digits in the beginning bound value.
hh:mi:ss.sssssssignhh:mi	PERIOD(TIME(n) WITH TIME ZONE), where n is the maximum number of fractional seconds digits in the beginning and ending bound values, or, if the ending bound value is UNTIL_CHANGED, the number of fractional seconds digits in the beginning bound value.
YYYY-MM-DD	PERIOD(DATE).
YYYY-MM-DD hh:mi:ss	PERIOD(TIMESTAMP(0)).
YYYY-MM-DD hh:mi:sssignhh:mi	PERIOD(TIMESTAMP(0) WITH TIME ZONE).
YYYY-MM-DD hh:mi:ss.ssssss	PERIOD(TIMESTAMP(n)), where n is the maximum number of fractional seconds digits in the beginning and ending bound values, or, if the ending bound value is UNTIL_CHANGED, the number of fractional seconds digits in the beginning bound value.
YYYY-MM-DD hh:mi:ss.sssssssignhh:mi	PERIOD(TIMESTAMP(n) WITH TIME ZONE), where n is the maximum number of fractional seconds digits in the beginning and ending bound values, or, if the ending bound value is UNTIL_CHANGED, the number of fractional seconds digits in the beginning bound value.

Element Types

The element type of a Period literal is derived from the format of the DateTime values specified in the string literal.

IF...	THEN the element type of the literal is...
the beginning bound value only has a date value	DATE.
the beginning bound value only has a time value and a time zone interval is not specified after either the beginning or ending bound value	TIME(n).
the beginning bound value only has a time value and a time zone interval is specified after the beginning or ending bound value	TIME(n) WITH TIME ZONE.
the beginning bound value has both a date value and time value and a time zone interval is not specified after either the beginning or ending bound value	TIMESTAMP(n).
the beginning bound value contains both a date value and time value and a time zone interval is specified after the beginning or ending bound value	TIMESTAMP(n) WITH TIME ZONE.

Usage Notes

A Period literal starts with keyword PERIOD, followed by a string literal that indicates the start (beginning bound value) and end (ending bound value) of the Period. Some of the syntactic elements and their usage that are not obvious are described below.

IF...	THEN...
the years, months, days, hours, minutes, seconds, and time zone interval do not conform to the rules of a DateTime literal with respect to the number of digits allowed and the valid range	a syntax error is reported.
the ending bound value is not UNTIL_CHANGED	the following must be true: The beginning and ending bound values must both have a date value or both not have a date value. The beginning and ending bound values must both have a time value or both bound values must not have a time value. Otherwise, a syntax error is reported.
the ending bound value is UNTIL_CHANGED and the beginning bound is a Timestamp value	the ending bound value must not be followed by a time zone interval; otherwise, a syntax error is reported.
the ending bound value is UNTIL_CLOSED (Teradata temporal tables only)	the following must be true: The data type of the beginning bound value must be comparable with TIMESTAMP(6) WITH TIME ZONE. The literal is only valid in an assignment operation where the target column to which the result is assigned is a transaction-time column. Because the only way to set the value of a transaction-time column is by using nontemporal DML, the literal is only valid in a nontemporal DML statement. Performing nontemporal operations on temporal tables requires the NONTEMPORAL privilege. For more information see Teradata Vantage™ - Temporal Table Support , B035-1182 .
the beginning bound value has a time value and the ending bound value is not UNTIL_CHANGED	the precision of the literal, n, is the maximum of the number of fractional digits specified in the beginning bound value and in the ending bound value.
the beginning bound value has a time value and the ending bound value is UNTIL_CHANGED	the precision of the literal, n, is the number of fractional digits specified in the beginning bound value.
only one of them includes a time zone value	the time zone field of the other is set to the current session time zone displacement. If both include time zone values, the result bounds include the corresponding time zone value.
UNTIL_CHANGED is specified for the ending bound value and the element type of the literal is TIME(n) [WITH TIME ZONE]	a syntax error is reported.
the element type of the literal is TIMESTAMP(n) WITH TIME ZONE	the result ending element is set to the maximum TIMESTAMP[(n)] WITH TIME ZONE value at UTC (that is, the time zone displacement for the ending bound is INTERVAL '00:00' HOUR TO MINUTE). If the ending bound value is UNTIL_CHANGED, the result ending element is set to the maximum TIMESTAMP value.
the element type of the literal is TIME or TIMESTAMP and the beginning or ending bound value contains leap seconds	the seconds portion gets adjusted to 59.999999 with the precision truncated to the result precision.
the element type of the literal is DATE	the result ending bound must be greater than the result beginning bound; otherwise, a syntax error is reported.
the element type of the literal is not DATE after adjusting both the beginning and ending bound values to UTC using the specified time zone interval or, if not specified, the current session time zone displacement	the result ending bound must be greater than the result beginning bound; otherwise, a syntax error is reported.
the beginning element of a period literal is specified as UNTIL_CHANGED or UNTIL_CLOSED	an error is reported.

Restrictions

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.

Example: PERIOD(DATE) Literal

The following INSERT statement uses a PERIOD(DATE) literal.

INSERT INTO Policy
   (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
   VALUES (497201, 304779902, 'AU', 'STD-CH-524-WXY-00',
           PERIOD '(2005-02-03, 2006-02-04)');

The following INSERT statement uses a PERIOD(DATE) literal and sets the ending bound to UNTIL_CHANGED.

INSERT INTO Policy
   (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity)
   VALUES (541008, 246824626, 'AU', 'STD-CH-345-NXY-00',
           PERIOD '(2009-10-01, UNTIL_CHANGED)');

Example: PERIOD(TIME[(n)] WITH TIME ZONE) Literal

In the following UPDATE statement, the PERIOD(TIME(0) WITH TIME ZONE) column for flight 243 from Los Angeles, CA to Orlando, FL is updated in the schedule table. The literal's element type is TIME(0) WITH TIME ZONE.

UPDATE schedule
   SET flight_period = PERIOD '(08:00:00-08:00 - 15:40:00-05:00)'
   WHERE flight_no = 243;