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')