How to manipulate dates in SQL (demo)

This is a reminder on how to manipulate dates in SQL (Oracle).

Setup:

I'll create a simple table a populate it with a few data:
create table date_test(id number, date1 date, ts1 timestamp, date2 date, ts2 timestamp);

insert into date_test values (1, 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'), 
to_date('2014-06-27', 'RRRR-MM-DD'));

Data selection examples:

SELECT CURRENT_TIMESTAMP FROM DUAL;

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '1' SECOND(1) 
FROM DUAL;

SELECT  TO_CHAR (TS1, 'RRRR-MM-DD, HH24:MI:SS') AS ORI, 
TO_CHAR (TS1-INTERVAL '1' SECOND(1),'RRRR-MM-DD, HH24:MI:SS') AS FORMATTED 
FROM DATE_TEST;

See also this page.