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
(2) Explanation of specification format
(3) Rules

(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]}

To Page Top

(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.

To Page Top

(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
Labeled duration qualifier	`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

To Page Top