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;?:O8lEA"k
相差秒數51Testing軟體測試網JSr$~ o6G ^2e[
----------51Testing軟體測試網5lI(Jl1F1]
     86401
%w ~7]R5i01 row selected51Testing軟體測試網uX0{2y KKGN,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$hE 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`3CXHr&E[
         151Testing軟體測試網8p}f;X+^:O!y1p
1 row selected51Testing軟體測試網h:S5r9FA
*/