TO_DSINTERVAL function

The TO_DSINTERVAL function converts a string representing a time unit to an INTERVAL DAY TO SECOND literal value. This function can also accept a number and a string as its arguments, and return an INTERVAL value with a single time-unit precision of DAY, HOUR, MINUTE, or SECOND.

You can use the TO_DSINTERVALfunction with a single argument (or with two arguments, its synonym, NUMTODSINTERVAL ) to specify an interval range value when you are defining a range-interval storage distribution strategy to fragment a table or an index.

Syntax

Numeric to INTERVAL

|--+-TO_DSINTERVAL---+--(--number--,--+-'--DAY--'----+--)-------|
   '-NUMTODSINTERVAL-'                +-'--HOUR--'---+      
                                      +-'--MINUTE--'-+      
                                      '-'--SECOND--'-'      

String to INTERVAL

|--TO_DSINTERVAL--(--' DD HH:MM:SS '--)-------------------------|
Element Description Restrictions Syntax
DD One or two digits specifying the number of days in the interval Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string
HH:MM:SS Three groups of two digits, separated by colon ( : ) symbols, specifying the numbers of hours, of minutes, and of seconds in the interval Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string
number A number specifying the number of days, hours, minutes, or seconds in the interval.

This can be an expression, including a column expression, that resolves (or can be cast) to one of the valid number data types.

 Must be one of the following data types:
  • INT
  • BIGINT
  • SMALLINT
  • INT8
  • DECIMAL
  • REAL
  • FLOAT
  • SERIAL
  • SERIAL8
  • BIGSERIAL
 Literal number

Usage

You can use the TO_DSINTERVAL function to specify an interval value when you fragment a table or index by an interval. The TO_DSINTERVAL function is valid in any context where a built-in routine is allowed. The NUMTODSINTERVAL function is a synonym to the TO_DSINTERVAL function for converting numeric values.

The following examples show how different values for the TO_DSINTERVAL function are interpreted.

The following examples specify an interval of one day:

TO_DSINTERVAL('1 00:00:00')
TO_DSINTERVAL(1,'DAY')
NUMTODSINTERVAL(1,'DAY')

The following examples specify an interval of one hour:

TO_DSINTERVAL('0 01:00:00')
TO_DSINTERVAL(1,'HOUR')
NUMTODSINTERVAL(1,'HOUR')

The following examples specify an interval of one minute and 30 seconds:

TO_DSINTERVAL('0 00:01:30')
TO_DSINTERVAL(1.5,'MINUTE')
NUMTODSINTERVAL(1.5,'MINUTE')

The following example shows how to use an expression as a numeric value:

TO_DSINTERVAL(10+10+100,'DAY')