How to calculate Timestamps in Oracle Database

As we all know, current time is obtained using current_timestamp keyword in Oracle. Most of the time we have observed that whenever we try to calculate using current_timestamp by subtracting or adding any digit to it e.g. current_timestamp + 20, if we're lucky it will work else in most cases it will throw us "SQL Error: ORA-00911: invalid character" error. The reason for this is that we are trying to calculate timestamp but without using its appropriate format. I have mentioned the ways below according to which timestamps should be calculated.


To obtain the current time we use following command:-

SQL>  select current_timestamp from dual;

CURRENT_TIMESTAMP
--------------------------------------
27-APR-11 03.08.29.433503000 PM EUROPE/LONDON

1 row selected.



To obtain current time to be adjusted in terms of no .of days we use following command:-

1. Retrieve no. of days to be adjusted


SQL>   select NUMTODSINTERVAL(-1, 'DAY') FROM dual;

NUMTODSINTERVAL(-1,'DAY')
-------------------------
-1 0:0:0.0

1 rows selected



2. Calculate time by adjusting in terms of days


SQL>   select current_timestamp + numtodsinterval( -1, 'day' ) current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------
26-APR-11 03.13.20.609585000 PM EUROPE/LONDON

1 rows selected




To obtain current time to be adjusted in terms of no. of months following command is used:-


1. Retrieve no. of months to be adjusted

SQL>   select numtoyminterval( 3, 'month' ) current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------
0-3

1 rows selected


2. Calculate time by adjusting in terms of months

SQL>   select current_timestamp + numtoyminterval( 3, 'month' ) current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------
27-JUL-11 03.31.49.913584000 PM EUROPE/LONDON

1 rows selected



To obtain current time to be adjusted in terms of no .of years we use following command:-

1. Retrieve no. of years to be adjusted

SQL>   select numtoyminterval( -2, 'year' ) current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------
-2-0

1 rows selected


2. Calculate time by adjusting in terms of years

SQL>   select current_timestamp + numtoyminterval( -2, 'year' ) current_timestamp from dual;

CURRENT_TIMESTAMP
-----------------
27-APR-09 03.34.35.035263000 PM EUROPE/LONDON

1 rows selected

P.S.: current_timestamp keyword written after the calculation is used as an alias name for the result field