因今天做個考勤統計的功能,要統計出請假/加班的時長,有兩種方法
第一種思路是,兩個時間相減,擷取兩個時間差之後,對所得的結果進行切割,擷取天數和具體時間差。
首先做的是取兩個時間相減
select to_char(t.kssj,'yyyy-mm-dd hh24:mi:ss') as kssj,
to_char(t.jssj,'yyyy-mm-dd hh24:mi:ss') as jssj,(t.jssj-t.kssj) as 時間差 from tf_em_kqjl t
這樣可以擷取到兩個時間點的內插補點
1 2014-05-16 17:37:31 2014-06-29 18:37:34 +000000044 01:00:03.000000
2 2014-05-09 17:38:00 2014-05-15 19:38:06 +000000006 02:00:06.000000
3 2014-05-22 17:38:50 2014-05-30 17:38:53 +000000008 00:00:03.000000
4 2014-05-10 17:08:41 2014-05-23 17:08:44 +000000013 00:00:03.000000
時間差的由天數和時:分:秒組成,是以采取的是切割substr
select to_char(t.kssj,'yyyy-mm-dd hh24:mi:ss') as kssj,
to_char(t.jssj,'yyyy-mm-dd hh24:mi:ss') as jssj,(t.jssj-t.kssj) as 時間差,
trunc(to_number(substr((t.jssj-t.kssj),1,instr(t.jssj-t.kssj,' ')))) 天,
substr((t.jssj-t.kssj),instr(t.jssj-t.kssj,' '),instr(t.jssj-t.kssj,' ')) 時間 from tf_em_kqjl t
這樣擷取到了天數和相差的時間
1 2014-05-16 17:37:31 2014-06-29 18:37:34 +000000044 01:00:03.000000 44 01:00:03.0
2 2014-05-09 17:38:00 2014-05-15 19:38:06 +000000006 02:00:06.000000 6 02:00:06.0
3 2014-05-22 17:38:50 2014-05-30 17:38:53 +000000008 00:00:03.000000 8 00:00:03.0
4 2014-05-10 17:08:41 2014-05-23 17:08:44 +000000013 00:00:03.000000 13 00:00:03.0
這時候不能直接切割時間,會報出錯誤,無效數字,這種無法得繼續切割下去,顯得很麻煩。然後sum出在總的天數
第二種思路是用months_between函數計算出秒,sum出總的秒數後用java進行運算
select t.kssj,t.jssj,months_between(t.jssj,t.kssj) m,months_between(t.jssj,t.kssj)*31*24*3600 as 秒,(case t.kqlx when '請假' then months_between(t.jssj,t.kssj)*31*24*3600 end) qj ,
(case t.kqlx when '加班' then months_between(t.jssj,t.kssj)*31*24*3600 end) jb from tf_em_kqjl t
1 16-5月 -14 05.37.31.000000下午 29-6月 -14 06.37.34.000000 下午 1.42070004480287 3805203 3805203
2 09-5月 -14 05.38.00.000000下午 15-5月 -14 07.38.06.000000 下午 0.1962387992154 525606 525606
3 22-5月 -14 05.38.50.000000下午 30-5月 -14 05.38.53.000000 下午 0.258065636200717 691203 691203
4 10-5月 -14 05.08.41.000000下午 23-5月 -14 05.08.44.000000 下午 0.419355958781362 1123203 1123203
這樣下來統計就顯得非常的容易,隻要sum(QJ/JB) 就可以統計出加班的時間和請假的時間(秒數)
//qj 為請假的總秒數
int day = (int)qj/(24*3600);
System.out.println(day);
int hour = (int)(qj%(24*3600))/3600;
System.out.println(hour);
int min = (int)((qj%(24*3600))%3600)/60;
System.out.println(min);
int sec = (int)qj%60;
System.out.println(sec);