Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do I get the minutes between two dates ?

Re: How do I get the minutes between two dates ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 25 Jan 2000 13:12:57 -0500
Message-ID: <9rpr8sc4mq3q2p7fheol8sug8nqus76i7l@4ax.com>


A copy of this was sent to "Paul Jeynes" <jeynesp_at_postoffice.co.uk> (if that email address didn't require changing) On Tue, 25 Jan 2000 17:32:39 -0000, you wrote:

>I've got a slight problem with some date manipulation which I initially
>thought would be straightforward. I'm loading data into a table through SQL
>Loader. The data contains two date fields which record the start and end
>time of a telephone call (in format 'DD/MM/YYYY HH24:MI:SS')
>
>I've got a BEFORE INSERT trigger on the table within which I want to
>calculate into another field, the duration of the call. Therefore I
>subtract the end date from the start date.
>
>This gives me the duration of the call as a numeric fraction of a day. I'd
>like to store the duration field as hours, minutes and seconds (not fussy if
>it's held in a varchar either).
>
>I've done the normal multiply by 24 and 60 to get it into minutes but this
>still gives the seconds as a decimal rather than as true seconds. I've also
>tried various date masks without success so now I'm really confused.
>
>Am I missing something ?
>
>Any help would be appreciated.
>

see http://osi.oracle.com/~tkyte/Misc/DateDiff.html

basically

 trunc( sysdate-created ) "Dy", 
 trunc( mod( (sysdate-created)*24, 24 ) )  "Hr", 
 trunc( mod( (sysdate-created)*24*60, 60 ) )  "Mi", 
 trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec", 


sysdate is your end_date, created is your start_date.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 25 2000 - 12:12:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US