Period Data Types: Basic Definitions | Data Types & Literals | Teradata Vantage - Period Data Types: Basic Definitions - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

Period

A Period is an anchored duration. It represents a set of contiguous time granules within that duration. It has a beginning bound (defined by the value of a beginning element) and an ending bound (defined by the value of an ending element). The representation of the period is inclusive-exclusive; that is, the period extends from the beginning bound up to but not including the ending bound. The following diagram represents a period of 9 days starting from 1 February, 2006 to 10 February, 2006. The Period includes 1 February, 2006 and extends up to, but does not include, 10 February, 2006.



Element Type

The element type of a Period data type is the data type of the beginning and ending elements of a value of that Period data type. The element type can be any DateTime data type. The DateTime data types are DATE, TIME, and TIMESTAMP. The TIME and TIMESTAMP data types have a number (from 0 to 6) of fractional seconds in the seconds field which can be specified or defaults to 6 (for example, TIME(3) and TIMESTAMP(6)). They can also explicitly include a time zone field by specifying WITH TIME ZONE (if WITH TIME ZONE is not specified, a time zone field is implicitly not included).

Note that the element type must be the same for both the beginning and ending elements of a period.

Comparable Period Data Types

Two Period values are comparable if their element types are of same DateTime data type. The DateTime data types are DATE, TIME, and TIMESTAMP.

Data type ... Is comparable with a ...
PERIOD(DATE) PERIOD(DATE) data type.
PERIOD(TIME(n) [WITH TIME ZONE]) PERIOD(TIME(m) [WITH TIME ZONE]) data type.
PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) PERIOD(TIMESTAMP(m) [WITH TIME ZONE]) data type.

Teradata extends this to allow a CHARACTER and VARCHAR value to be implicitly cast as a Period data type for some operators and, therefore, have a Period data type. Since the Period data type is the data type of the other operand, these operands will be comparable.

Note that DateTime and Period data are saved internally with the maximum precision of 6 although the specified precision may be less than this and is padded with zeroes. Thus, the comparison operations with differing precisions work without any additional logic. Additionally, the internal value is saved in UTC for a Time or Timestamp value, or for a Period value with an element type of TIME or TIMESTAMP. All comparable operands can be compared directly due to this internal representation irrespective of whether they contain a time zone value, or whether they have the same precision. Note that the time zone values are ignored when comparing values.

Assignment Operators

A value is only assignable to a Period type target if either one of the following is true:
  • The source and target both have Period data types with the same element type. That is, the element types are both DATE, both TIME, or both TIMESTAMP.
  • It is possible to implicitly cast the source (which must have a CHARACTER or VARCHAR data type) as the data type of the target. In this case, the source is implicitly cast as the data type before assignment.

In addition, the target precision must not be lower than the source precision.

A Period value is not assignable to a target that does not have a Period data type.

Time Granule

A time granule or, simply, granule is the minimum interval that can be represented at a given precision. For example, if the element type of a period is DATE, the granule is one day (that is, INTERVAL '1' DAY); if the element type of a period is TIME(0), the granule is one second (that is, INTERVAL '1' SECOND); if the element type of a period is TIMESTAMP(2), the granule is one hundredth of a second (that is, INTERVAL '0.01' SECOND).

Last Value

The last value of a period is the greatest value of the element type that is less than the value of the ending element of the period. That is, the last value is the ending bound minus one granule of the element type.

Duration

The duration of a period is the number of granules in a period and is represented as an interval.