Oracle计算时间差表达式

--获取两时间的相差豪秒数
IK1w9~-?._5L0select 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;
c rq.ye F0/*51Testing软件测试网Dog5Y;v3km6k:H7Fq
相差豪秒数51Testing软件测试网,W Yg9Z'`!?m,U|
----------
W+wH!Jx*r!?/S#l/T0  8640100051Testing软件测试网o+r[fE
1 row selected
wW ?7~we0*/51Testing软件测试网ZBG5p|h1m N
51Testing软件测试网4\+n0P} |3A
--获取两时间的相差秒数
X,o
]JU A#N.R4?T`0select 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;
yU.{
]H*C0/*51Testing软件测试网8A-Gc8U_5K!a
相差秒数51Testing软件测试网 h7}%r9W)QVoI*e0M
----------51Testing软件测试网7U [bgVI.sR/r
     8640151Testing软件测试网"LP;u T4^!~&G
1 row selected
g?oW9z*b/P mw
M0*/51Testing软件测试网
{9D3aug
af
x$E_
51Testing软件测试网Udkb\g1}S;K;[vq
--获取两时间的相差分钟数
m'nW|Lk)C;h#p0select 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;51Testing软件测试网/k;TB%CN._l${:s5v-Z
/*51Testing软件测试网Kbv$X
~D6K*E
相差分钟数51Testing软件测试网 f`Z-eFK5GTWCW
----------51Testing软件测试网r!n:^b4bW ^:v
      1441
Z[u%YC#m01 row selected
2R,` M*E8ujmk l0*/51Testing软件测试网&G6Oa g [1p
51Testing软件测试网F2AW3zb/^PpEY
--获取两时间的相差小时数51Testing软件测试网F:j&Jya } RH
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)  相差小时数FROM DUAL;
9`7Jm0J'I{UZ,U oVr0/*51Testing软件测试网D V+|6]6z2C)u&v
相差小时数
2FWCH
Y&\5~ Au0----------
:ll7`cK5M0        2551Testing软件测试网V(D%Emg#_yX5WU
1 row selected51Testing软件测试网u@v&v%z6V7Mb8I
*/51Testing软件测试网D}&M)qUw[3fQ
51Testing软件测试网%sM ~e1l2V ~+k{
--获取两时间的相差天数
.f#L9|g"i s8o2H&t0select 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;51Testing软件测试网'hrei,R^@
/*51Testing软件测试网2T.B,S#p K"d
相差天数51Testing软件测试网(H2H o Exz,` s'xD
----------
/LCi+c%tVZ0         251Testing软件测试网CznU J?N
1 row selected
e(r @&Hs`$Jh0*/
9Z*ia9POkx
Y%z051Testing软件测试网.X
WpKud*JE6T
--获取两时间月份差
3A$h,LGA/l+lX0select (EXTRACT(yearFROM to_date('2009-05-01','yyyy-mm-dd'))- EXTRACT(yearFROM to_date('2008-04-30','yyyy-mm-dd')))* 12+ 51Testing软件测试网$v)k;|'G;K+C.C2k J
       EXTRACT(monthFROM to_date('2008-05-01','yyyy-mm-dd'))- EXTRACT(monthFROM to_date('2008-04-30','yyyy-mm-dd')) months
Q4y0H N-d'lA,C n0from dual;
$^}Wl%l'E+U*g7v2B~0/*MONTHS----------        131 row selected*/
/N_a/j7NB051Testing软件测试网*W+rE4Yg_G
--获取两时间年份差51Testing软件测试网V@PK~7~Sp
select EXTRACT(yearFROM to_date('2009-05-01','yyyy-mm-dd'))- EXTRACT(yearFROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;
.x#P/vl9Lew`0/*51Testing软件测试网/b_'rX*[BIc"n%v
YEARS
l!A|\+J*d*H%pL0----------
+\$E,w._0o;Vi0         151Testing软件测试网K
NC8c*Z E
1 row selected
2q$yh;M3k;^#H_,X0*/