Oracle计算时间差表达式

--获取两时间的相差豪秒数
(q.nH J CA0select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL;51Testing软件测试网(e}&t-c^
/*
Re5p2BV$B5ByD
_0相差豪秒数
.H b r)eiy0----------
(W(@EjX5_0  86401000
:? k8^8LUP`01 row selected
-hQ0l^d S7n0*/51Testing软件测试网Z`$q,b;H9O&x;/O

n7[ nm5zj|s0--获取两时间的相差秒数
A&~5[7c't*r-B0select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;
O6cj)m3c4C&d0/*51Testing软件测试网0J;?:O8lE
A"k
相差秒数51Testing软件测试网JSr$~ o6G ^2e[
----------51Testing软件测试网5lI(Jl1F1]
     86401
%w ~7]R5i01 row selected51Testing软件测试网uX0{2y KK
GN,q
*/
Z H1k#K;g;u])EU051Testing软件测试网5_N]0b1wi
--获取两时间的相差分钟数51Testing软件测试网:U Af3a+XI1d+L
select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60)  相差分钟数 FROM DUAL;
:Ypl-mp$h
E oW0/*
'^-@%aUn/E.cu8h k0相差分钟数51Testing软件测试网AzmE'STwM3hY
----------
3nd%tB,H w+yL0      144151Testing软件测试网Kn!r1Cy/n W
1 row selected51Testing软件测试网c%E3a!?K:uy
*/
"?$_ Ed0e0
3_Eh0I WE&|0--获取两时间的相差小时数
ct*Y {+f E@x0select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24)  相差小时数 FROM DUAL;51Testing软件测试网,}8Ff i?7Lf
/*51Testing软件测试网#/U/4r0{XQ+c
相差小时数
rT%o6vU0----------
7|/z5IK(/}0        2551Testing软件测试网xO7L'`%p&d i6N
1 row selected
5E0o7|Vm0*/
qI G w` Bf{)Eb051Testing软件测试网 Jto'Z Z/X.{6d
--获取两时间的相差天数51Testing软件测试网P"P(f2B mY-Zc
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')))  相差天数 FROM DUAL;
uA RfU6D0/*51Testing软件测试网%T0U0wiWQ u
相差天数
:ZDFgh;QK L0}Y0----------
(E/3FB4XT o0         251Testing软件测试网rK,O,B2bt6G
1 row selected51Testing软件测试网%up Jp_+Y
*/
4WQQ0q4d*?J#zD051Testing软件测试网WP6~$^'s&?
--获取两时间月份差
/*{2d GTK,Jv1DS0select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 + 51Testing软件测试网&XKO*k^-uF#G.T
       EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months51Testing软件测试网} x(E8rk
from dual;
9L1u[;P-nFF0/* MONTHS---------- 131 row selected */
C"_z:Wvfg1fWt`051Testing软件测试网q*LQ(zM
--获取两时间年份差
4D*U Hq MT]0select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;
qJV'V&]'K0/*
Wj5nb n]0YEARS51Testing软件测试网&b6n U.@ i
----------51Testing软件测试网0`3CXH
r&E[
         151Testing软件测试网8p}f;X+^:O!y1p
1 row selected51Testing软件测试网h:S5r9FA
*/