時間處理的函數

Calculating Working Days Without Using a Function

This tip comes from Mike Selvaggio, Senior DB Architect and DBA, Orsel Consulting Inc. in North Brunswick, NJ.

If you need to calculate working days between 2 dates and you can't create the Oracle-recommended working days function, here is the SQL that can accomplish the same task:

SQL> set linesize 100
SQL> desc date_test

 Name                                              Null?   Type
 --------------------------------------------   --------   ------------
 START_DT                                                  DATE
 END_DT                                                    DATE

SQL> select * from date_test
  2  .
SQL> /

START_DT  END_DT
--------- ---------
13-DEC-02 18-DEC-02
17-DEC-02 19-DEC-02
18-DEC-02 23-DEC-02
26-DEC-02 28-DEC-02

SQL>  select start_dt, end_dt, end_dt - start_dt age, 
SQL> work_days(start_dt, end_dt) from date_test;

START_DT  END_DT           AGE WORK_DAYS(START_DT,END_DT)
--------- --------- ---------- --------------------------
13-DEC-02 18-DEC-02          5                          3
17-DEC-02 19-DEC-02          2                          2
18-DEC-02 23-DEC-02          5                          3
26-DEC-02 28-DEC-02          2                          1


SQL> get workingdays
  1  select
  2  start_dt,
  3  end_dt,
  4  trunc(end_dt - start_dt) age,
  5  (trunc(end_dt - start_dt) -
  6   (
  7    (case
  8     WHEN (8-to_number(to_char(start_dt,'D') )) > trunc(end_dt - start_dt)+1
  9     THEN 0
 10     ELSE
 11      trunc( (trunc(end_dt - start_dt) - (8-to_number(to_char(start_dt,'D') ))) / 7 ) + 1
 12     END) +
 13    (case
 14     WHEN mod(8-to_char(start_dt,'D'),7) > trunc(end_dt - start_dt)-1
 15     THEN 0
 16     ELSE
 17      trunc( (trunc(end_dt-start_dt) - (mod(8-to_char(start_dt,'D'),7)+1)) / 7 ) + 1
 18     END)
 19   )
 20  ) workingdays
 21* from date_test

SQL> /

START_DT  END_DT           AGE WORKINGDAYS
--------- --------- ---------- -----------
13-DEC-02 18-DEC-02          5           3
17-DEC-02 19-DEC-02          2           2
18-DEC-02 23-DEC-02          5           3
26-DEC-02 28-DEC-02          2           1

  
  

Date/Time from Substraction of Two Date Values 
This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development, Towers Perrin in Voorhees, NJ. 
This technique presents conversion of the result of subtraction between dates. For example, I am looking for how many days, hours, minutes, etc. elapsed between two dates. Oracle Database is returning the result in days (such as 2.23456). The result is not exactly readable and has to be formatted. In most cases programmers convert the result using PL/SQL with different functions, and this is OK. But why not ask Oracle Database to do that with Oracle precision and speed? Please see the standard and the three "alternative" approaches below. 
For example, For example, after a job ends, I have that job as running: 

5.12345 days

def Dif=5.12345

1. PL/SQL Approach: 

--A few extra variables are included for the better viewing of temporary results

set serveroutput on

Declare 

Months	Number;
Days    Number;
Hours   Number;
Minutes Number;
Seconds  Number;

Diff    Number:=&Dif ;
Diff0   Number;
Diff1   Number;
Diff2   Number;

Begin

Days:=FLOOR(Diff);

Diff0:=Mod(Diff,1);

dbms_output.put_line ( 'Days - '||Days||'  Mod is '||Diff0);

Hours:=FLOOR(Diff0*24);

Diff1:=Mod(Diff0*24,1);

dbms_output.put_line ( 'Hours - '||Hours||'  Mod 1 is '||Diff1);


Minutes:=FLOOR(Diff1*60);

Diff2:=MOD(Diff1*60,1);

Seconds:=Floor(Diff2*60);

dbms_output.put_line ( 'Minutes - '||Minutes||'  Seconds - '||Seconds);


dbms_output.put_line ( 'Minutes - '||Minutes||'  Seconds - '||Seconds);

end;
/

-- Output: 

Days - 5  Mod is .12345
Hours - 2  Mod 1 is .9628
Minutes - 57  Seconds - 46

Final: Days - 5 Hours - 2 Minutes - 57 Seconds - 46

PL/SQL procedure successfully completed.

2. SQL Method: 


select
	Days,
	A,
	TRUNC(A*24)				Hours,
	TRUNC(A*24*60 - 60*TRUNC(A*24))		Minutes,
	TRUNC(A*24*60*60 - 60*TRUNC(A*24*60))		Seconds,
	TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60))	mSeconds
from
(
select
	trunc(&DIF)		Days,
	&Dif - trunc(&DIF)	A
from dual
)
;

-- Output: 


      DAYS          A      HOURS    MINUTES    SECONDS   MSECONDS
---------- ---------- ---------- ---------- ---------- ----------
         5     .12345          2         57         46          8

3. Alternative Approach ("nice-and-easy"): 
-- I will use a leap year date, 01/01/2000 for example, for temporary purposes. -- This date will provide accurate calculation for most cases. 

select
	to_number(SUBSTR(A,1,4)) - 2000	Years,
	to_number(SUBSTR(A,6,2)) - 01		Months,
	to_number(SUBSTR(A,9,2)) - 01		Days,
	SUBSTR(A,12,2)			Hours,
	SUBSTR(A,15,2)			Minutes,
	SUBSTR(A,18,2)			Seconds
from
(
select
	to_char(to_date('20000101','YYYYMMDD') + &Dif,'YYYY MM DD HH24:MI:SS')	A
from dual
)
;

     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
         0          0          5 02 57 46

All results are identical, but think about which method is easiest. And here are more examples: 
A. 
Def Dif=14.2879
1.
Final: Days - 14 Hours - 6 Minutes - 54 Seconds - 34 
2. 

      DAYS          A      HOURS    MINUTES    SECONDS   MSECONDS
---------- ---------- ---------- ---------- ---------- ----------
        14      .2879          6         54         34         56
3. 

     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
         0          0         14 06 54 35
B. Def Dif=351.1124 
1.
Final: Days - 351 Hours - 2 Minutes - 41 Seconds - 51 
2.

      DAYS          A      HOURS    MINUTES    SECONDS   MSECONDS
---------- ---------- ---------- ---------- ---------- ----------
       351      .1124          2         41         51         36
3.

     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
         0         11         16 02 41 51
C. Def Dif=794.7734 
1. 
Final: Days - 794 Hours - 18 Minutes - 33 Seconds - 41 
2. 

      DAYS          A      HOURS    MINUTES    SECONDS   MSECONDS
---------- ---------- ---------- ---------- ---------- ----------
       794      .7734         18         33         41         76
3. 

     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
         2          2          4 18 33 42
As you see, method #3 (Alternative Approach ("nice-and-easy")) is not the only alternative, but it is very representative and accurate with ALL calculations (done by Oracle Database). I used the first day of a leap year especially for precision purposes.