51Testing软件测试网
e-iT$ru^L)y}1.日期时间间隔操作
51Testing软件测试网Z*T!LGJyM/H 当前时间减去7分钟的时间
51Testing软件测试网'Q bto9loVW select sysdate,sysdate - interval '7' MINUTE from dual
m6jNknhV0 当前时间减去7小时的时间
+s0jY'r;F-?y:t0 select sysdate - interval '7' hour from dual
~ys!BQ"j"X0 当前时间减去7天的时间
6T o8l zjRWo0 select sysdate - interval '7' day from dual
51Testing软件测试网,V+G B!\v#G
P7t 当前时间减去7月的时间
51Testing软件测试网`N
y2OE W select sysdate,sysdate - interval '7' month from dual
E%|z;{~|]
\0 当前时间减去7年的时间
9`*p~%n-~0 select sysdate,sysdate - interval '7' year from dual
F'^HwABj,n0g8L0 时间间隔乘以一个数字
51Testing软件测试网#G;h/sh6d5jEC select sysdate,sysdate - 8 *interval '2' hour from dual
51Testing软件测试网P0Z8rw4]
q]s-S2.日期到字符操作
eB*um&sktU0 select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
+A3]Ke3}wJ0 select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
hw|p(J$QA6Ix0 select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
51Testing软件测试网x8R'f1A2S+M6b1` select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
BynZ;i8QKqb5^0 参考
oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
2{)o:csv8S S%{03. 字符到日期操作
51Testing软件测试网/_/w+c6i5Z select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
9NEV{2Rp,F0 具体用法和上面的to_char差不多.
)f)c,^5B.l'|04. trunk/ ROUND函数的使用
HxDN-X)jI[
\)|0 select trunc(sysdate ,'YEAR') from dual
ha\1uE+o;_!h ab0 select trunc(sysdate ) from dual
Hx.`WyH F5C.N|0 select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual
3M&?P*lX;Hq~05.oracle有毫秒级的数据类型
51Testing软件测试网W?&UB*f
w?2s --返回当前时间 年月日小时分秒毫秒
7@1pD(opknZ1r0 select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
51Testing软件测试网yp/L%A_od$e7t --返回当前 时间的秒毫秒,可以指定秒后面的精度(最大=9)
51Testing软件测试网j&gQ:YiVy
v select to_char(current_timestamp(9),'MI:SSxFF') from dual;
kD'j*CI!ioL,Q8`c06.计算程序运行的时间(ms)
^`u4xK0 declare
(Jo&K{.s
mZ0 type rc is ref cursor;
51Testing软件测试网/Nm'VAx
d
ae l_rc rc;
pB|:T7U.W:U0 l_dummy all_objects.object_name%type;
51Testing软件测试网4Tw4`Y.n1X
V}8w1_ l_start number default dbms_utility.get_time;
P/['{~ i"Y.Tj6c0 begin
51Testing软件测试网5xe1F'Iv%_q"bj for I in 1 .. 1000
%x%k${?:U7_2\e-o0 loop
51Testing软件测试网0Z&h&g:M|tR9` open l_rc for
0`9MbUCF)A0 'select object_name from all_objects '||
FW+mBd:xV0 'where object_id = ' || i;
51Testing软件测试网)z0u9?Ih Ws5]v fetch l_rc into l_dummy;
51Testing软件测试网L2`,ZW E0N close l_rc;
5S Td|T2hSb0 end loop;
0}2`,T"I5_{Y4F7K0 dbms_output.put_line
#H/I&{|(D"D9T0 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
%KC2JAw0yUl0 ' seconds...' );
2~$x
|@7]TT6X0 end;