how to add second in oracle timestamp

Question

How can I add seconds with timestamp value in Oracle. I tried this ....

SELECT CURRENT_TIMESTAMP , CURRENT_TIMESTAMP+200 AS addedTime FROM dual

But by this I am getting this

2018-06-04 19:03:01 => 2018-12-21 19:03:01 (after adding 200)

As you can see date is getting added , but I want to add second only...

This SQL running properly in DB2 , can any one suggest me any suitable alternative in Oracle.

Answer 1

In Oracle date/time arithmetic is expressed in terms of days. So adding 200 adds 200 days to an Oracle DATE or TIMESTAMP object. If you want to add seconds you can either use intervals or you can add fractional days. Personally, for something as granular as seconds I prefer to use intervals; anything from hours and up I use regular date arithmetic or ADD_MONTHS() (interval months and years are particularly dangerous as they are not leap-year safe, while ADD_MONTHS() is).

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '200' SECOND FROM dual;

Note that the single quotes around the interval value are necessary; INTERVAL 200 SECOND will raise an error.

Hope this helps.

Answer 2

You could always use fraction(for Oracle 1 = 1 day):

SELECT CURRENT_TIMESTAMP ,
       CURRENT_TIMESTAMP + 200 *(1/24/60/60) AS addedTime 
FROM dual;
<=>
SELECT CURRENT_TIMESTAMP + 200/86400 FROM dual

DBFiddle Demo

or:

SELECT CURRENT_TIMESTAMP + INTERVAL '200' SECOND FROM dual