Hitachi

Hitachi Advanced Database SQL Reference

7.29.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]}

To Page Top

(2) Explanation of specification format

value-expression-primary:

Specify SMALLINT, INTEGER, or BIGINT type data for value-expression-primary. For details about the value expression primary, see (1) Specification format in 7.21.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.28.1 Specification format and rules for datetime operations.

To Page Top

(3) Rules

  1. Depending on the data type for which the datetime operation is performed, the labeled duration qualifier that can be specified vary, as shown in the following table.

    Table 7‒50: Labeled duration qualifier that can be specified

    Labeled duration qualifier

    Data type for 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.

  2. If you specify a dynamic parameter by itself for value-expression-primary, the dynamic parameter will be assumed to be BIGINT type#.

    #

    If the integer data type format is a legacy format, it will be an INTEGER type.

  3. The NOT NULL constraint does not apply to the value of the labeled duration result (the null value is allowed).

  4. If the result of the value-expression-primary is a null value, the result of the labeled duration will be a null value.

  5. The trailing S in YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS, and PICOSECONDS can be omitted. The following are examples:

    Example: When specifying 1 year

    1 YEAR or 1 YEARS

  6. The labeled interval specified for the window frame value specification of a window function can only specify a value specification for the value-expression-primary.

  7. The following table shows the fractional seconds precision assumed when a labeled duration qualifier is specified.

    Table 7‒51: Assumed fractional seconds precision

    Specified labeled duration qualifier

    Assumed fractional seconds precision

    MILLISECOND

    3

    MICROSECOND

    6

    NANOSECOND

    9

    PICOSECOND

    12

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.