-- Manipulate hours, minutes and seconds in a date

-- Display sysdate
alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

select sysdate from dual;

SYSDATE
-----------------
20060511 18:30:27

-- Add 23 hours 59 minutes and 59 seconds
select trunc(sysdate) + (23/24) + (59/(24*60)) + (59/(24*60*60)) from dual;

TRUNC(SYSDATE)+(2
-----------------
20060511 23:59:59

-- Substract 8 hours
select sysdate-(8/24) from dual;

SYSDATE-(8/24)
-----------------
20060511 10:30:30


-- How can I get the time difference between two date columns?
select to_char(start_time,'yyyy-mm-dd:hh24:mi:ss') start_time, to_char(end_time,'yyyy-mm-dd:hh24:mi:ss') end_time, 
       lpad(round(floor((end_time-start_time)*24*60*60)/3600),2,'0')
       || ':' ||
       lpad(floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60),2,'0')
       || ':' ||
       lpad(round((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600 -
       (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60))),2,'0') elapsed, output_bytes  
from v$rman_backup_job_details 
order by start_time;

-- How do I add 5 minutes to a date?
select sysdate +5/(24*60) from dual;

-- How do I select a date minus 5 years and 1 day
select sysdate, 
to_date(to_char(to_number(to_char(sysdate-1,'YYYY'))-5)||to_char(sysdate-1,'MM')||
to_char(to_number(to_char(sysdate-1,'DD'))),'YYYYMMDD') 
from dual;

-- Display a date in French
select to_char(to_date('January 15, 1989','Month dd, yyyy','nls_date_language = AMERICAN')
     ,'Month dd, yyyy','nls_date_language = FRENCH') from dual
;