Period
A Period is an anchored duration that represents a set of contiguous time granules within that duration. A Period has a beginning bound (the value of the beginning element) and an ending bound (the value of the 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—DATE, TIME, or 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)). These types 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.
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.
| Data Type | Comparable Data Type |
|---|---|
| PERIOD(DATE) | PERIOD(DATE) |
| PERIOD(TIME(n) [WITH TIME ZONE]) | PERIOD(TIME(m) [WITH TIME ZONE]) |
| PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) | PERIOD(TIMESTAMP(m) [WITH TIME ZONE]) |
Depending on the operator, Teradata may extend this rule to allow a CHARACTER and VARCHAR value to be implicitly cast as a Period data type (and therefore have a Period data type). Because the Period data type is the data type of the other operand, these operands are comparable.
DateTime and Period data are saved internally with maximum precision of 6, but the specified precision may be less than 6 and padded with zeroes. Therefore, comparison operations with differing precisions work. 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. Due to this internal representation, comparable operands can be compared directly, regardless of presence or absence of time zone value or difference in precision. Time zone values are ignored when comparing values.
Assignment Operators
- The source and target have Period data types with the same element type. That is, the element types are both DATE, both TIME, or both TIMESTAMP.
- The source (which must have a CHARACTER or VARCHAR data type) can be implicitly cast as the data type of the target. The implicit cast is done 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.