7.28.1 Specification format and rules for labeled durations
A labeled duration is used in datetime operations to represent a specific time duration. The format is a numeric value followed by a duration keyword (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, PICOSECOND). A labeled duration can be specified only in a window frame clause, or as the second operand of an addition or subtraction on datetime data.
- Organization of this subsection
(1) Specification format
labeled-duration ::= value-expression-primary labeled-duration-qualifier labeled-duration-qualifier ::={YEAR[S]|MONTH[S]|DAY[S] |HOUR[S]|MINUTE[S]|SECOND[S] |MILLISECOND[S]|MICROSECOND[S] |NANOSECOND[S]|PICOSECOND[S]}
(2) Explanation of specification format
- value-expression-primary:
-
Specify SMALLINT or INTEGER type data for value-expression-primary. For details about the value-expression-primary, see (1) Specification format in 7.20.1 Specification format and rules for value expressions.
- labeled-duration-qualifier:
-
labeled-duration-qualifier ::= {YEAR[S] | MONTH[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] | MILLISECOND[S] | MICROSECOND[S] | NANOSECOND[S] | PICOSECOND[S]}
Specifies one of the following.
- YEAR[S]:
-
Expresses a duration in years.
The range of numeric data that can be specified in value-expression-primary is -9,998 to 9,998.#
- MONTH[S]:
-
Expresses a duration in months.
The range of numeric data that can be specified in value-expression-primary is -119,987 to 119,987.#
- DAY[S]:
-
Expresses a duration in days.
The range of numeric data that can be specified in value-expression-primary is -3,652,058 to 3,652,058.#
- HOUR[S]:
-
Expresses a duration in hours.
The range of numeric data that can be specified in value-expression-primary is -87,649,415 to 87,649,415.#
- MINUTE[S]:
-
Expresses a duration in minutes.
The range of numeric data that can be specified in value-expression-primary is -5,258,964,959 to 5,258,964,959.#
- SECOND[S]:
-
Expresses a duration in seconds.
The range of numeric data that can be specified in value-expression-primary is -315,537,897,599 to 315,537,897,599.#
- MILLISECOND[S]:
-
Expresses a duration in milliseconds.
The range of numeric data that can be specified in value-expression-primary is -315,537,897,599,999 to 315,537,897,599,999.#
- MICROSECOND[S]:
-
Expresses a duration in microseconds.
The range of numeric data that can be specified in value-expression-primary is -315,537,897,599,999,999 to 315,537,897,599,999,999.#
- NANOSECOND[S]:
-
Expresses a duration in nanoseconds.
The range of numeric data that can be specified in value-expression-primary is -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807.#
- PICOSECOND[S]:
-
Expresses a duration in picoseconds.
The range of numeric data that can be specified in value-expression-primary is -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807.#
- #
-
When a labeled duration in a datetime operation is multiplied, the range given for value-expression-primary applies to the value of the product. For example, if you specify "C1" DAYS *"C2", the range that can be specified for (C1*C2) is -3,652,058 to 3,652,058.
For the rules concerning multiplication and division in datetime operations that include labeled durations, see (e) Rules for multiplication and division of labeled durations in (4) Rules in 7.27.1 Specification format and rules for datetime operations.
(3) Rules
-
The labeled duration qualifiers that can be specified vary depending on the data type on which the datetime operation is performed, as shown in the following table.
Table 7‒38: Labeled duration qualifiers that can be specified Labeled duration qualifier
Data type of datetime operation
DATE
TIME
TIMESTAMP
YEAR
Y
N
Y
MONTH
Y
N
Y
DAY
Y
N
Y
HOUR
N
Y
Y
MINUTE
N
Y
Y
SECOND
N
Y
Y
MILLISECOND
N
Y
Y
MICROSECOND
N
Y
Y
NANOSECOND
N
Y
Y
PICOSECOND
N
Y
Y
- Legend:
-
Y: Can be specified.
N: Cannot be specified.
-
If you specify a dynamic parameter for value-expression-primary, the assumed data type of the dynamic parameter will be INTEGER.
-
The NOT NULL constraint does not apply to the result value of the labeled duration (the null value is allowed).
-
If the result of value-expression-primary is a null value, the result of the labeled duration will be a null value.
-
Specification of the final S in YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS, and PICOSECONDS is optional, as illustrated in the following example:
- Example: To specify one year:
-
1 YEAR or 1 YEARS
-
When you specify a labeled duration for window-frame-value-specification, only a value specification can be specified for value-expression-primary in the labeled duration.
-
The following table shows the fractional seconds precision that is assumed when you specify a labeled duration qualifier.
Table 7‒39: Assumed fractional seconds precision Specified labeled duration qualifier
Assumed fractional seconds precision
MILLISECOND
3
MICROSECOND
6
NANOSECOND
9
PICOSECOND
12