Timestamp manipulation

Get the timestamp 10 minutes earlier from now:

SELECT SYSTIMESTAMP - INTERVAL '10' minute FROM DUAL;


Get the timestamp 1 year later from the specified timestamp:

SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1) FROM dual;


Extract fields from a timestamp:

SELECT
EXTRACT(year FROM current_timestamp) EY,
EXTRACT(month FROM current_timestamp) EM,
EXTRACT(day FROM current_timestamp) ED,
EXTRACT(hour FROM current_timestamp) EH,
EXTRACT(minute FROM current_timestamp) EM,
EXTRACT(second FROM current_timestamp) ES,
EXTRACT(timezone_hour FROM current_timestamp) TH,
EXTRACT(timezone_minute FROM current_timestamp) TM,
EXTRACT(timezone_region FROM current_timestamp) TR,
EXTRACT(timezone_abbr FROM current_timestamp ) TA
FROM dual;

More on: Oracle Timestamp Data Type