NUMTODSINTERVAL

Syntax

NUMTODSINTERVAL
(
  number  IN double precision,
  unit    IN text
)
RETURNS interval;

Overview

The NUMTODSINTERVAL function interprets the given numeric value as a specific unit (e.g., ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’) and converts it to the INTERVAL type.

Numeric values are divided into integer and decimal parts.

• If 'DAY', the integer part is converted to day and the decimal part is converted to hours, minutes, and seconds by multiplying by 24.

• If 'HOUR', the integer part is converted to hours and the decimal part is converted to minutes and seconds by multiplying by 60.

• If 'MINUTE', the integer part is converted to minutes and the decimal part is converted to seconds

  by multiplying by 60.

• If 'SECOND', the entire number is interpreted as a second

If an invalid unit is passed, an error occurs.

Parameter

Parameter	Description
number	double precision type; the time value to convert. If there is a decimal part, it is converted to a lower unit (hour, minute, second).
unit	text type; It specifies the unit of the numeric value. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’, and ‘SECOND’. For example, if ‘DAY’ is specified, the decimal part is converted to hours, minutes, and seconds in that order.

Example

-- 1.5 DAYS is converted to 1 day of 12 hours.
SELECT oracle.NUMTODSINTERVAL(1.5, 'DAY');

 numtodsinterval 
-----------------
 1 day 12:00:00
(1 row)

-- 2.75 HOURS is converted to 2 hours 45 minutes.
SELECT oracle.NUMTODSINTERVAL(2.75, 'HOUR');

 numtodsinterval 
-----------------
 02:45:00
(1 row)

-- 30 MINUTE is converted to 30 minutes 0 seconds.
SELECT oracle.NUMTODSINTERVAL(30, 'MINUTE');

 numtodsinterval 
-----------------
 00:30:00
(1 row)

-- 90 SECOND returns exactly 90 seconds.
SELECT oracle.NUMTODSINTERVAL(90, 'SECOND');

 numtodsinterval 
-----------------
 00:01:30
(1 row)