How do I a convert a number to an INTERVAL of minutes?

oracle date-arithmetic
Hank Gay picture Hank Gay · Aug 22, 2011 · Viewed 7.2k times · Source

In pseudo-Oracle, I want to do the following:

select systimestamp + to_interval(select NUMERIC_COLUMN from SOME_TABLE where SOME_TABLE_PK = :stuff) from dual;

If the number of minutes were always the same, I could use an interval literal a la interval '360' minute, but I can't find a simple function to convert a number to a MINUTE interval. What am I missing?

Answer

Vincent Malgrat picture Vincent Malgrat · Aug 22, 2011

You can use the numtodsinterval function which does exactly the conversion you need (number to interval):

SQL> select systimestamp, systimestamp + numtodsinterval(20, 'MINUTE') from dual;

SYSTIMESTAMP              SYSTIMESTAMP+NUMTODSINTERVAL(2
------------------------- -------------------------------
2011-08-22 16:12:24.060   2011-08-22 16:32:24.060

Related questions

Calculate difference between 2 date / times in Oracle SQL

I have a table as follows: Filename - varchar Creation Date - Date format dd/mm/yyyy hh24:mi:ss Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss How can I calcuate the difference in hours …

Read More
Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More