2.12 Time operations

Organization of this section
(1) Function
(2) Data eligible for operations
(3) Labeled duration
(4) Format
(5) Explanation
(6) Format of a time operation and the data type of the result
(7) Rules for time operations
(8) Rules for addition and subtraction of time data and time interval data
(9) Rules for addition or subtraction between time interval data, and multiplication or division of time interval data by an integer
(10) Notes

(1) Function

Time operations enable retrieval and updating involving times and time intervals to be performed.

The time function supports the following operations: subtraction on times; addition and subtraction of the time between time intervals; addition and subtraction on time intervals; multiplication and division of a time interval by an integer.

(2) Data eligible for operations

Table 2-16 shows the values of time data and time interval data on which time operations can be performed.

Table 2-16 Time operation data

OperationApplicable time operation values
Time dataTime interval data
Addition
  • Time interval data (INTERVAL HOUR TO SECOND)
  • Literals that express time intervals as decimal numbers
  • Labeled duration (HOUR[S], MINUTE[S], SECOND[S])
  • Time data (TIME)
  • Literals in which time is specified in a predefined character string representation
  • Time interval data (INTERVAL HOUR TO SECOND)
  • Literals that express time intervals as decimal numbers
Subtraction
  • Time data (TIME)
  • Literals in which time is specified in a predefined character string representation
  • Time interval data (INTERVAL HOUR TO SECOND)
  • Literals that express time intervals as decimal numbers
  • Labeled duration (HOUR[S], MINUTE[S], SECOND[S])
  • Time interval data (INTERVAL HOUR TO SECOND)
  • Literals that express time intervals as decimal numbers
Multiplication, DivisionNot specifiableInteger data (INTEGER, SMALLINT)
Unary operationNot specifiable in time data or as a labeled duration

(3) Labeled duration

A labeled duration used in time operations expresses specific units of time as numeric values followed by interval keywords. A labeled duration can be specified only in the second operand for addition or subtraction of time interval data relative to time data.

(4) Format

(value-expression) {HOUR[S]|MINUTE[S]|SECOND[S]}

(5) Explanation

  1. The following items can be specified in a value expression:
    • Integer literals
    • Column literals
    • SQL variable or SQL parameter
    • Arithmetic operations
    • Set functions
    • Scalar functions
    • CASE expressions
    • CAST specification
    • Function call
    • Scalar subquery
  2. The value expression must have an integer data type (SMALLINT or INTEGER).
  3. HOUR[S], MINUTE[S], and SECOND[S] indicate the units of hours, minutes, and seconds, respectively. The S suffix is optional.
    Specification examples are given below:
    1 hour: 1 HOUR
    23 minutes: 23 MINUTES
    100 seconds: 100 SECONDS
  4. The following ranges of values can be specified in a value expression:
    HOUR[S]: -23 to 23
    MINUTE[S]: -1439 to 1439
    SECOND[S]: -86399 to 86399

(6) Format of a time operation and the data type of the result

Table 2-17 shows the relationships between the format of a time operation and the data type of the result.

Table 2-17 Relationships between the format of time operation and the data type of the result

Operation formatData type of result
time-data - time-dataTime interval data type
time-data {+|-} {time-interval-data|label-duration}Time data type
time-interval-data + time-dataTime data type
time-interval-data {+|-} time-interval-dataTime interval data type
time-interval-data {*|/} integer-dataTime interval data type

Note: Embedded variables, indicator variables, and ? parameters cannot be specified in a time operation involving time data or time interval data.

(7) Rules for time operations

For the rules for performing time operations, see the rules below and the rules provided in section 2.9 Value expressions, value specifications, and item specifications.

(a) Rules for subtracting one time data item from another
(b) Explanation of the algorithm

[Figure]

(8) Rules for addition and subtraction of time data and time interval data

  1. The result of addition or subtraction of time data or time interval data is the time data type.
  2. The allowable range of the result of a computation is from 0:0:0 through 23:59:59.
  3. Time interval data (not a labeled duration) is computed in the order of hour, minute, and second.

(9) Rules for addition or subtraction between time interval data, and multiplication or division of time interval data by an integer

  1. The results of these computations take the date interval data type.
  2. The result of an operation should be in the following range: -99 hours, 59 minutes, 59 seconds to 99 hours, 59 minutes, 59 seconds.
  3. If the result of a time computation is greater than 60 minutes or 60 seconds, the result is carried to the next hour or minute, respectively.
  4. Division is performed by converting a given time value into seconds. Any digits following the decimal point in the result are rounded off.

(10) Notes

The rules below for time operations do not result in an error when the overflow error suppression feature is set:

For details about the operational results produced when the overflow error suppression feature is set, see 2.18 Operational results with overflow error suppression specified.