Re: Compute difference between date/time

X-NEWS: mcvax0 comp.databases.oracle: 3403 Relay-Version: VMS News - V6.0-3 14/03/90 VAX/VMS V5.5; site inet.d48.lilly.com Path: inet.d48.lilly.com!mentor.cc.purdue.edu!purdue!haven.umd.edu!uunet!zaphod.mps.ohio-state.edu!  howland.reston.ans.net!agate!ames!kronos.arc.nasa.gov!maas-neotek.arc.nasa.gov!blah Newsgroups: comp.databases.oracle
Subject: Re: Compute difference between date/time fields problem. From: blah_at_maas-neotek.arc.nasa.gov (MJW/TWF) Date: Fri, 19 Mar 1993 19:07:09 GMT
Sender: References: <1993Mar19.093058.1_at_mcvax2.d48.lilly.com> Organization: NASA Ames Research Center
Nntp-Posting-Host: maas-neotek.arc.nasa.gov Lines: 66

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
>
>
>Mark

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.

Brian Stanek Received on Mon Mar 22 1993 - 18:39:02 CET