INTERVAL scalar function

The INTERVAL function converts a character string representation of an interval to a decimal duration.

The schema is SYSIBM.

Syntax

Read syntax diagramSkip visual syntax diagram INTERVAL ( string-constant )
string-constant
A character string representation of an interval, for example:
'4 years 2 months 3 days'
'3 day 4 year 2 month'
'-4y -2 m -3d'
'-2 hr -21 min -34sec'
'4years 2months 3 days 2 hours 21minutes 34seconds 75 milliseconds 27 microseconds'
'2 mons 3 days 4 yrs 2 hrs 20 mins 30 secs 75 ms 27 us'
Note:
  • The input must be a string constant, not an expression or column value (sqlcode SQL0171N with SQLSTATE=42815).
  • A blank between each value and its unit keyword is optional.
  • Values must be either all positive or all negative.
  • The unit keywords are case-insensitive.
  • The order of the unit keywords is unimportant.
  • A unit keyword cannot be used more than once.
  • The number that is specified for each unit must be a whole number.
  • The following statements are equivalent:
    INTERVAL (string-constant)
CAST (string-constant as INTERVAL)
The specified interval can be of one of the following types:
time interval
The input value does not contain units other than hours, minutes, or seconds, and cannot exceed the equivalent of '99h 99m 99s' (sqlcode SQL0105N with SQLSTATE=42604). A minutes value that exceeds 99 is converted to hours, and a seconds value that exceeds 99 is converted to minutes. For example, 100 seconds is converted to 1 minute and 40 seconds.
date interval
The input value does not contain units other than years, months, or days, and cannot exceed the equivalent of '9999y 99m 99d' (sqlcode SQL0105N with SQLSTATE=42604). A months value that exceeds 99 is converted to years, and a days value that exceeds 99 is converted to 30-day months. For example, 100 days is converted to 3 months and 10 days.
timestamp interval
The input value cannot exceed the equivalent of '9999y 99m 99d 99h 99m 99s 999999us' (sqlcode SQL0105N with SQLSTATE=42604). The millisecond and microsecond values are combined into a single microsecond value before they are evaluated. If the resulting value exceeds 999999 microseconds, it is converted to seconds. For example, '88 ms 5000000 us' is converted to 5088000 microseconds. This exceeds 999999 microseconds, so it is converted to 5.088000 seconds. A seconds value that exceeds 99 is converted to minutes. A minutes value that exceeds 99 is converted to hours. An hours value that exceeds 99 is converted to days. A days value that exceeds 99 is converted to 30-day months. A months value that exceeds 99 is converted to years.
Table 1. Keywords for representing time units
Keywords Maximum value allowed when specifying a...
time interval date interval timestamp interval
year, years, yrs, yr, y (not allowed) 9999 9999
month, months, mons, mon (not allowed) 119999 119999
day, days, d (not allowed) 3599999 3599999
hour, hours, hrs, hr, h 99 (not allowed) 86399999
minute, minutes, mins, min, m 5999 (not allowed) 2147483647
second, seconds, secs, sec, s 359999 (not allowed) 2147483647
millisecond, milliseconds, ms (not allowed) (not allowed) 2147483647
microsecond, microseconds, us (not allowed) (not allowed) 2147483647

Result

The data type of the result depends on the type of the input interval. If the input value represents:
  • A date interval, the result is a date duration, which is a DECIMAL(8,0) value.
  • A time interval, the result is a time duration, which is a DECIMAL(6,0) value.
  • A timestamp interval, the result is a timestamp duration, which is a DECIMAL value. The precision of this value depends on whether, after combining millisecond and microsecond values into a single microsecond value, and after then converting any microseconds in excess of 999999 to seconds, the number of microseconds remaining is zero:
    • If so, the result is DECIMAL(14,0)
    • If not, the result is DECIMAL(20,6)

Examples

  • The following statement returns the DECIMAL(8,0) value 40203:
    interval('4years 2months 3days')
  • The following statement returns the DECIMAL(6,0) value -122030:
    interval('-12 hours -20 minutes -30 seconds')
  • The following statement returns the DECIMAL(20,6) value 40801092630.007055:
    interval('4 years 9 hour 26min 30 seconds 7 ms 55us 8months 1d')
  • The following statement returns the DECIMAL(14,0) value 22035:
    interval('2 hours 20 minutes 30 seconds 1500 ms 3500000 us')
    The millisecond (1500) and microsecond (3500000) values are combined into a single microsecond value (5000000) before they are evaluated. The resulting value exceeds 999999 microseconds, so it is converted to 5 seconds, which are added to the specified 30 seconds for a total of 35 seconds.
  • The following statement returns the DECIMAL(8,0) value 90714, which corresponds to 9 years, 7 months, and 14 days.
    interval('1 years 100 months 104 days')
    Because the number of days exceeds 99, the 104 days are converted to 3 30-day months, plus a remainder of 14 days. The 3 months are added to the specified 100 months for a total of 103 months. Because the number of months exceeds 99, the 103 months are converted to 8 12-month years, plus a remainder of 7 months. The 8 years are added to the specified 1 year for a total of 9 years.
  • The following statement returns the DECIMAL(6,0) value 230120, which corresponds to 23 hours, 1 minute, and 20 seconds. 
    interval('20 hours 181 minutes 20 seconds')
    Because the number of minutes exceeds 99, the 181 minutes are converted to 3 60-minute hours, plus a remainder of 1 minute. The 3 hours are added to the specified 20 hours for a total of 23 hours.