你个好东西，tom的datediff
Getting the difference between DatesFrequently we are asked -- how can I find the number of minutes between two dates or what is the amount of elapsed time.With Oracle Dates, this is pretty trivial, you can get either TOTAL (days, hours, minutes, seconds) between 2 dates simply by subtracting them or with a little mod'ing you can get Days/Hours/Minutes/Seconds between.
For example: SQL> set serveroutput on
SQL>
SQL> declare
2
a date;
3
b date;
4begin
5
a := sysdate;
6
dbms_lock.sleep(10);-- sleep about 10 seconds give or take
7
b := sysdate;
8
9
dbms_output.put_line( b-a || ' of a day has elapsed' );
10
dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
11
dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
12
dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
13end;
14/
.000127314814814814814814814814814814814815 of a day has elapsed
.00305555555555555555555555555555555555556 of an hour has elapsed
.1833333333333333333333333333333333333336 of a minute has elapsed
11.00000000000000000000000000000000000002 seconds has elapsed PL/SQL procedure successfully completed.
To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following: select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) )"Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) )"Mi",
trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-created "Tdy",
(sysdate-created)*24 "Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60 "Tsec"
from all_users
where rownumcreate or replace function datediff( p_what in varchar2,
2
p_d1 in date,
3
p_d2 in date ) return number
4as
5l_resultnumber;
6begin
7select (p_d2-p_d1) *
8
decode( upper(p_what),
9
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
10 into l_result from dual;
11
11return l_result;
12end;
13/ Function created
Now, i just create a view to demonstrate with:
SQL> create or replace view temp_view
2as
3select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,
4 to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2
5from dual
6/
View created.
SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;SECONDS
----------
6269539 SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;MINUTES
----------
104492.317 SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;HOURS
----------
1741.53861
[size=-1]
All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.
|
