2.12 Time operations
(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
The following table lists the time data or time interval data and applicable time operation values.
Table 2-19 Time operation data
Operation | Applicable time operation values |
---|
Time data | Time 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, Division | Not specifiable | Integer data (INTEGER, SMALLINT) |
Unary operation | Not 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
- 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
- The value expression must have an integer data type (SMALLINT or INTEGER).
- 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
- 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
The following table indicates the relationships between the format of a time operation and the data type of the result.
Table 2-20 Relationships between the format of time operation and the data type of the result
Operation format | Data type of result |
---|
time-data - time-data | Time interval data type |
time-data {+|-} {time-interval-data|label-duration} | Time data type |
time-interval-data + time-data | Time data type |
time-interval-data {+|-} time-interval-data | Time interval data type |
time-interval-data {*|/} integer-data | Time 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
(8) Rules for addition and subtraction of time data and time interval data
- The result of addition or subtraction of time data or time interval data is the time data type.
- The allowable range of the result of a computation is from 0:0:0 through 23:59:59.
- Time interval data (not a labeled duration) is computed in the order of hour, minute, and second.
- The result of operations using time data that includes a leap second is as follows:
- If the number of seconds is greater than or equal to 60, this is implicitly changed to 59 before the operation is executed.
Examples:
The result of the operation '12:34:60' + 1 MINUTE is '12:35:59'.
The result of the operation '12:34:60' + 1 SECOND is '12:35:00'.
- Leap seconds are not included in the results of operations.
(9) Rules for addition or subtraction between time interval data, and multiplication or division of time interval data by an integer
- The results of these computations take the date interval data type.
- The result of an operation must be in the following range: -99 hours, 59 minutes, 59 seconds to 99 hours, 59 minutes, 59 seconds.
- 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.
- 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:
- Overflow occurs in the time data type.
- Overflow occurs in the time interval data type.
- Overflow occurs in the labeled duration data type.
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.