//求两个连续的时间之间的差值
dt1 dt2
05:10 05:20
23:15 01:30
//结果:
0.1h
2.3h
//或者
10m
135m
//以小时格式显示
with t as(
select '05:10' dt1,'05:20' dt2 from dual union all
select '23:15','01:30' from dual)
select round(
case when dt2 >= dt1
then dt2 - dt1
else dt2 + 1 -dt1
end * 24,1
) hour
from (
select to_date(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt1,'yyyy-mm-dd hh24:mi') dt1
,to_date(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt2,'yyyy-mm-dd hh24:mi') dt2
from t)
/
HOUR
----------
0.2
2.3
//解析:
//这里牵涉到时间的相减,我们都知道数字的相加减,但是时间的相加减,这个得牵涉到日期;
//所以我们将这个问题转换为连续的日期,然后再将两个日期相加减。
//我们都知道1天=24小时,所以将两个日期之差乘以24,表示两个日期之间相差的小时数目,
//round()函数取一位小数
//
//以分钟数显示
//原理与上面一样的,只是想日期差值转换为分钟数,1天=24小时=24*60分钟
with t as(
select '05:10' dt1,'05:20' dt2 from dual union all
select '23:15','01:30' from dual)
select case
when dt2 >= dt1
then dt2-dt1
else dt2 + 1 - dt1
end * 1*24*60 minute
from (
select to_date(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt1,'yyyy-mm-dd hh24:mi') dt1
,to_date(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt2,'yyyy-mm-dd hh24:mi') dt2
from t)
/
MINUTE
----------
10
135
//如果两个时间不是连续的,而是当天的,要求两个时间差值,可用下面的方法
//我们的做法是将两个时间值转化为同一天,然后转化为timestamp类型
//最后,将使用extract函数截取出其中的小时数和分钟数即可
with t as(
select '05:10' dt1,'05:20' dt2 from dual union all
select '23:15','01:30' from dual
)
select extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
from (
select to_timestamp(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt1||':00','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp(to_char(trunc(sysdate),'yyyy-mm-dd')||' '||dt2||':00','yyyy-mm-dd hh24:mi:ss') dt2
from t)
/
HOUR MINUTE
---------- ----------
0 10
-21 -45
//