duration data type

The xs:duration data type represents a duration of time that is expressed by the Gregorian year, month, day, hour, minute, and second components. Derived from data type xdt:anyAtomicType.

The range that can be represented by this data type is from -P83333333333333Y3M11574074074DT1H46M39.999999S to P83333333333333Y3M11574074074DT1H46M39.999999S (or -999999999999999 months and -999999999999999.999999 seconds to 999999999999999 months and 999999999999999.999999 seconds).

The lexical form of xs:duration is the ISO 8601 extended format PnYnMnDTnHnMnS. The following abbreviations describe the extended format:

P: The duration designator.
nY: n is an unsigned integer that represents the number of years.
nM: n is an unsigned integer that represents the number of months.
nD: n is an unsigned integer that represents the number of days.
T: The date and time separator.
nH: n is an unsigned integer that represents the number of hours.
nM: n is an unsigned integer that represents the number of minutes.
nS: n is an unsigned decimal that represents the number of seconds. If a decimal point appears, it must be followed by one to six digits that represent fractional seconds.

For example, the following form indicates a duration of 1 year, 2 months, 3 days, 10 hours, and 30 minutes:

P1Y2M3DT10H30M

The following form indicates a duration of negative 120 days:

-P120D

An optional preceding minus sign (-) indicates a negative duration. If the sign is omitted, a positive duration is assumed.

Reduced precision and truncated representations of this format are allowed, but they must conform to the following requirements:

If the number of years, months, days, hours, minutes, or seconds in any expression equals zero, the number and its corresponding designator can be omitted. However, at least one number and its designator must be present.
The seconds part can have a decimal fraction.
The designator T must be absent if and only if all of the time items are absent.
The designator P must always be present.

For example, the following forms are allowed:

P1347Y
P1347M
P1Y2MT2H
P0Y1347M
P0Y1347M0D

The form P1Y2MT is not allowed because no time items are present. The form P-1347M is not allowed, but the form -P1347M is allowed.

The Db2 database system stores xs:duration values in a normalized form. In the normalized form, the seconds and minutes components are less than 60, the hours component is less than 24, and the months component is less than 12. Each multiple of 60 seconds is converted to one minute, each multiple of 60 minutes to one hour, each multiple of 24 hours to one day, and each multiple of 12 months to one year. For example, the following XQuery expression invokes a constructor function specifying a duration of 2 months, 63 days, 55 hours, and 91 minutes:

xquery
xs:duration("P2M63DT55H91M")

In the duration, 55 hours is converted to 2 days and 7 hours, and 91 minutes is converted to 1 hour and 31 minutes. The expression returns the normalized duration value P2M65DT8H31M.