IBM SC41-5210-04 Time arithmetic operation in Query for iSeries, Durations in Query for iSeries

Time arithmetic operation in Query for iSeries

Times can be subtracted, incremented, or decremented. The result of subtracting one time (TIME2) from another (TIME1) is a time duration that specifies the number of hours, minutes, and seconds between the two times.

For example, the result of TIME(’11:02:26’) − (’10:32:56’) is 2930; a duration of 0 hours, 29 minutes, and 30 seconds.

Incrementing and decrementing times in Query for iSeries

The result of adding or subtracting a duration from time is itself a time. If a duration of hours is added or subtracted, only the hours portion of the time is affected. The minutes and seconds remain unchanged. If a duration of minutes is added or subtracted, only the minutes and hours if necessary are affected. Adding or subtracting a duration of seconds affects the seconds portion and possibly the minutes and hours.

Timestamp arithmetic operation in Query for iSeries

Timestamps can be subtracted, incremented, or decremented. The result of adding or subtracting a duration from a timestamp is a timestamp. Date and time arithmetic is the same as previously defined, except that an overflow or underflow of hours is carried into the date portion of the result.

Durations in Query for iSeries

A duration represents a length of time. There are four types: labeled, date, time, and timestamp.

Labeled duration in Query for iSeries

A labeled duration represents a specific unit of time expressed as a number followed by one of the following duration keywords:

YEAR, YEARS

MONTH, MONTHS

DAY, DAYS

HOUR, HOURS

MINUTE, MINUTES

SECOND, SECONDS

MICROSECOND, MICROSECONDS

A labeled duration can only be used as an operand if the other operand is of type date, time or timestamp. For example:

HIREDATE + 2 MONTHS + 14 DAYS

is a valid expression.

HIREDATE + (2 MONTHS + 14 DAYS)

is not a valid expression. In both expressions, the labeled durations are 2 MONTHS and 14 DAYS.

Date duration in Query for iSeries

A date duration represents years, months, and days expressed as a DECIMAL (8,0) number. To be properly interpreted, the number must have the formatyyyymmdd where yyyy represents the number of years, mm the number of months, and dd the number of days. The result of subtracting one date value from another, as in the expression HIREDATE — BIRTHDATE, is a date duration. Example:

HIREDATE - BIRTHDATE

Time duration in Query for iSeries

A time duration represents hours, minutes and seconds expressed as a DECIMAL (6,0) number. To be properly interpreted , the number must have the format hhmmss where hh represents the number of hours,mm the number of minutes, and ss the number of seconds. The result of subtracting one time value from another is a time duration.

Chapter 5. Defining result fields in Query for iSeries 73