RE: Compute difference between date/time fields problem.
Date: 19 Mar 93 09:30:58 EST
Message-ID: <1993Mar19.093058.1_at_mcvax2.d48.lilly.com>
>>I have a table that contains a date/time field. Sometime later
>>I would like Oracle to compute the difference between the current
>>date/time and the data field. This result should be expressed in
>>minutes.
>>
>>I would like to implement the solution through Pro-C. Could this
>>be done through SQLPLUS?
>>
>>Replies, e-mail or through the newsgroup, are appreciated.
>>
>>John
I have a table with start and complete dates. There was a need to produce a SQL Report with the time that it took for a report to be executed. I tried subtracting the dates but with you place a timestamp on the date field, it seems that Oracle will give you a julian number instead of a formatted date. To solve this problem I came up with the following SQL*Plus statement. This is crude but it works:
rem
rem Date: 24-Feb-93 rem Author: Brian Stanek (Profound Consulting) rem rem Description: This script will produce a report that shows all of rem the reports, for an application, who executed rem the report and the time to complete. If no application rem code is entered, all applications are displayed.rem
rem
rem column formats
rem
column rpt_name format a46 wrap heading 'Report Name' just center
column rqstr_user_id format a15 wrap heading 'User' just center
column execution_time format a15 heading 'Execution Time|DD:HH:MI:SS' just cent
column code_desc noprint new_value aptn_name
rem
rem Title
rem
ttitle center aptn_name skip 2
rem
rem Break points
rem
break on code_desc skip page on rpt_name skip 1
rem
rem Select statement
rem
select code_desc, rpt_name, rqstr_user_id, replace (
/* Number of days */
to_char( to_date(start_dtm,'DD-MON-YYYY') - to_date(cmplt_dtm,'DD-MON-YYYY'),'00') || ':' || /* Number of hours */ to_char ( 12 - to_number ( to_char( to_date(to_char(cmplt_dtm,'SSSSS') - to_char(start_dtm,'SSSSS'),'SSSSS'), 'HH') ) ,'00') || ':'|| /* Number of minutes and seconds */ to_char( to_date(to_char(cmplt_dtm,'SSSSS') - to_char(start_dtm,'SSSSS'),'SSSSS'), 'MI:SS') ,' ') Execution_Time
from rad_rpt_list rli, rad_rpt_usage rus, rad_validation rva
where rli.rpt_id = rus.rpt_id
and rli.aptn_code = nvl(upper('&aptn_code'),rli.aptn_code)
and rva.valid_code = rli.aptn_code
and rva.column_name = 'APTN_CODE'
order by 1,2
/
This statement produces the following report:
test glits reports
Execution Time
Report Name User DD:HH:MI:SS
---------------------------------------------- --------------- ---------------
GLT_14DB_UPD_DATES OPS$RZX2134 00:00:00:27
OPS$RZX2134 00:00:01:07
GLT_24LOT_CLOSE OPS$RZX2134 00:00:00:12
OPS$RZX2134 00:00:00:12
PRINT_POSTSCRIPT OPS$RZX2134 00:00:00:42
OPS$RZX2134 00:00:00:35
OPS$RZX2134 00:00:00:29
TABLE_INDEX OPS$RZX2134 00:00:00:46
OPS$RZX2134 00:00:00:45
OPS$RZX2134 00:00:00:39
OPS$RZX2134 00:00:00:38
If anyone else has a better way to do this, with or without PL/SQL, please post to this newsgroup (I cannot receive/send Email).
Brian Stanek
Profound Consulting
Disclaimer: I know nothing. (Hogan's Heros) Received on Fri Mar 19 1993 - 15:30:58 CET