IBM ISERIES SC41-5210-04, User Manual

Page: 85 / 294

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:
HI 2 14 DAYS
is a valid expression.
HI (2 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 format
yyyymmdd 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