Re: Compute difference between date/time

From: MJW/TWF <blah_at_maas-neotek.arc.nasa.gov>
Date: Mon, 22 Mar 1993 19:40:41 GMT
Message-ID: <1993Mar22.194041.28252_at_kronos.arc.nasa.gov>

>In article <1993Mar19.190709.7518_at_kronos.arc.nasa.gov> usenet_at_kronos.arc.nasa.gov (Will Edgington, wedgingt_at_ptolemy.arc.nasa.gov)
>>In article <1993Mar19.093058.1_at_mcvax2.d48.lilly.com> rzx2134_at_mcvax2.d48.lilly.com writes:
>>>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.
 [more stuff deleted]
>>Here's a slightly shorter solution, which takes advantage of the fact
>>that date-date returns the number of days between the 2 dates.
>>
>>SELECT code_desc, rpt_name, rqstr_user_id,
>>trunc(cmplt_dtm-start_dtm) || ':' ||
>>to_char(sysdate + (cmplt_dtm-start_dtm-trunc(cmplt_dtm-start_dtm)),
>>'HH24: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 works except it indicated an execution time of 00:14.16 took 12:14.16.
>When you subtract two dates with the timestamp, the to_char conversion
>will place a 12 in the hours. That is why the original select statment
>treated the hours apart from the minutes and seconds.

Actually, what you say is not quite true. My weakness was that I did this so that it just happened to work out right: the leading sysdate in the to_char function (which, incidentally, is there only to trick the weak SQL parser into thinking that the entire expression is a date) could have contained some time baggage. You could remove this, of course, by doing a TRUNC, but this might return a number. To be safe, then, here is the corrected statement:

SELECT code_desc, rpt_name, rqstr_user_id, trunc(cmplt_dtm-start_dtm) || ':' ||
to_char(to_date(1,'J') + (cmplt_dtm-start_dtm-trunc(cmplt_dtm-start_dtm)), 'HH24: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 uses the first Julian date, at midnight on January 1st, 4712 BC. Of course, since we are only looking at time, the fact that it is midnight (00:00:00) is the only fact of interest.

Note that this uses HH24, not HH, to display hours. This is obviously quite important, since only the 24 hour count will give you the true number of hours (the other wraps at 12).

Sorry if my orignial posting lead anyone astray ...

Mark Received on Mon Mar 22 1993 - 20:40:41 CET