How to add 10 seconds in current_timestamp SQL ( Oracle )

Question

I want to add 10 seconds to current_timestamp in my PL/SQL script. I tried below code but it's output is not in timestamp format.

SELECT CURRENT_TIMESTAMP+1/24/60/6 FROM dual; 

Output:

CURRENT_TIMESTAMP+1/24/60/6  --------------------------- 28-AUG-15 

Is there any function similar to TIMESTAMPADD in Mysql server?

Answer 1

In Oracle, if you want a timestamp as the result, rather than a date (a date always includes the time to the second, though, so you may just want a date), you'd want to add an interval to the timestamp. There are various ways to construct an interval-- you can use an interval literal

select current_timestamp + interval '10' second   from dual 

or you could use the numtodsinterval function

select current_timestamp + numToDSInterval( 10, 'second' )   from dual 

Answer 2

It can be achieved by using TO_CHAR

Select TO_CHAR(current_timestamp,'DD-MM-YY hh24:mi:SS') AS TIMESTAMP, TO_CHAR(current_timestamp+10/24/60/60,'DD-MM-YY hh24:mi:SS') AS TIMESTAMP_PLUS_10SEC from dual; 

OUTPUT:

   TIMESTAMP         TIMESTAMP_PLUS_10SEC 31-08-15 05:17:19     31-08-15 05:17:29