Oracle NUMTODSINTERVAL function

Last update on September 19 2024 12:21:32 (UTC/GMT +8 hours)

How to convert any number to an interval day to second literal?

The NUMTODSINTERVAL() function is used to convert a numeric value into an INTERVAL DAY TO SECOND literal. This function allows you to represent a number of days, hours, minutes, or seconds as an interval, which can be useful for various time-based calculations and operations.

Uses of Oracle NUMTODSINTERVAL() Function

Converting Numeric Values to INTERVAL DAY TO SECOND Literals: Convert numeric values to intervals of days, hours, minutes, or seconds.

Time-based Calculations: Use the interval to perform calculations involving date and time, such as determining time ranges or intervals.

Dynamic Interval Specifications: Specify intervals dynamically based on numeric values, which can be useful in various queries and operations.

Querying with Window Functions: Use the function in window functions, such as counting records within a specified interval in analytics queries.

Syntax:

NUMTODSINTERVAL(n, 'interval_unit')

Parameters:

Name	Description
n	Any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
interval_unit	A CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values: DAY HOUR MINUTE SECOND interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

Name

Description

Any NUMBER value or an expression that can be implicitly converted to a NUMBER value.

interval_unit

A CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype.
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

DAY
HOUR
MINUTE
SECOND

interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored.
By default, the precision of the return is 9.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Examples: Oracle NUMTODSINTERVAL() function

The following SQL query counts the number of employees hired by the same manager within the past 100 days from his or her hire date.

Sample table: employees

SQL> SELECT manager_id, first_name, hire_date,
 2     COUNT(*) OVER (PARTITION BY manager_id ORDER BY hire_date
 3     RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count
 4     FROM employees;;

Sample Output:

MANAGER_ID FIRST_NAME           HIRE_DATE               T_COUNT
---------- -------------------- -------------------- ----------
       100 Lex                  13-JAN-2001 00:00:00          1
       100 Den                  07-DEC-2002 00:00:00          1
       100 Payam                01-MAY-2003 00:00:00          1
       100 Michael              17-FEB-2004 00:00:00          1
       100 Matthew              18-JUL-2004 00:00:00          1
       100 John                 01-OCT-2004 00:00:00          2
       100 Karen                05-JAN-2005 00:00:00          2
       100 Alberto              10-MAR-2005 00:00:00          2
       100 Adam                 10-APR-2005 00:00:00          3
       100 Neena                21-SEP-2005 00:00:00          1
       100 Shanta               10-OCT-2005 00:00:00          2
. . .

Previous: NEXT_DAY
Next: NUMTOYMINTERVAL