James's Blog

Oracle: time/date operation

1. date interval

select sysdate, sysdate - interval '7' MINUTE from dual

select sysdate, sysdate - interval '7' day from dual

select sysdate, sysdate - 8*interval '2' hour from dual

2. date to char conversion
select sysdate, to_char(sysdate, 'yyy-mm-dd hh24:mi:ss') from dual

3. char to date conversion
select to_date('2005-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

4. trunk/round functions
select trunc(sysdate,'YEAR') from dual

5. using mili-second date type
select to_char(current_timestamp(9),'HH24:MI:SSxFF') from dual

6. calculate process runtime
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
begin
for i in 1 .. 1000
loop
open l_rc for 'select object_name from all_objects '
'where object_id = ' i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
(round( ( dbms_utility.get_time-l_start)/100, 2) ' seconds...');
end;

posted by James Pei @ 1:47 PM