Oracle time subtraction Gets a specific number of days, hours, minutes ____oracle

Because today do the function of attendance statistics, to count out the time of leave/overtime, there are two ways

The first idea is that two times subtract, get two time difference, the result of the cut, get the number of days and specific time difference.

The first thing to do is to subtract two times

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 time difference from TF_EM_KQJL t

This allows you to get a difference of two points in time

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

The time difference consists of days and hours: minutes: seconds, so take the cut 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 time difference,
trunc (To_number (substr ((T.JSSJ-T.KSSJ), 1,instr (T.JSSJ-T.KSSJ, '))) day,
substr ( T.JSSJ-T.KSSJ), InStr (T.JSSJ-T.KSSJ, '), InStr (T.JSSJ-T.KSSJ, ')) time from TF_EM_KQJL t
 

This gets the number of days and the time difference

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

At this time can not directly cut the timing, will report errors, invalid numbers, this can not continue to cut down, it seems very troublesome. Then sum out in total number of days

The second idea is to use the Months_between function to calculate the seconds, sum out the total number of seconds after the operation in Java

Select T.kssj,t.jssj,months_between (T.JSSJ,T.KSSJ) m,months_between (T.JSSJ,T.KSSJ) *31*24*3600 as sec, (case T.KQLX when ' Leave ' then Months_between (T.JSSJ,T.KSSJ) *31*24*3600 end QJ,
(case t.kqlx when ' overtime ' then Months_between (T.JSSJ, T.KSSJ) *31*24*3600 end) JB from TF_EM_KQJL t

1 1 June-May-14 05.37.31.000000 afternoon 2 September-June-14 06.37.34.000000 afternoon 1.42070004480287 3805203 3805203
2 September-May-14 05.38.00.000000 afternoon 1 May-May-14 07.38.06.000000 afternoon 0.1962387992154 525606 525606
3 2 February-May-14 05.38.50.000000 afternoon 30月-May-14 05.38.53.000000 afternoon 0.258065636200717 691203 691203
4 October-May-14 05.08.41.000000 afternoon 2 March-May-14 05.08.44.000000 afternoon 0.419355958781362 1123203 1123203

This is very easy to count, as long as SUM (QJ/JB) can be counted overtime and the time of absence (seconds)

QJ the total number of seconds to leave

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);