Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    28

    Wink Unanswered: How Calculate time between 2 date .

    For exemple we just want have this result with good format 'HH:MIS'


    select to_char(DAT_FIN_TRT,'HH:MIS') as "Heure_Fin_TRT" ,
    to_char(DAT_DEBUT_TRT,'HH:MIS') as "Heure_DEB_TRT" ,
    (To_char(DAT_FIN_TRT,'HHMISS') - to_char(DAT_DEBUT_TRT,'HHMISS') ) as durée
    from coeadwh.tp_controle_batch where rownum < 15;


    results :

    Star_JOB End_JOB LAST

    03:01:35 02:33:26 6809
    03:12:16 02:33:32 7884
    03:04:31 02:33:44 7087
    03:11:07 02:33:28 7779
    03:03:01 02:33:26 6975
    01:16:29 01:14:41 188
    01:31:50 01:14:56 1694
    01:19:42 01:15:03 439
    01:19:41 01:15:11 430
    01:17:33 01:17:03 30
    01:18:35 01:15:43 292

    We juste want for exemple for the first row : Last = 28 minutes and 09 seconds !!!
    and so on for the all the others lines ...

    Is somebody has got an idea ...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This should do it:
    Code:
    SELECT start_job
    ,      end_job
    ,      to_char(floor(days*24)) || ':' 
           || ltrim(to_char(floor(days*24*60) - floor(days*24)*60,'00')) || ':'
           || ltrim(to_char(floor(days*24*60*60) - floor(days*24*60)*60,'00')) as time
    from (select start_job, end_job, end_job-start_job days from mytable);
    You could make that complex expression for "time" into a function so that your query becomes:
    Code:
    select start_job
    ,      end_job
    ,      format_time(end_job-start_job) time from mytable;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You can also try something like:

    Code:
    
    select start_job,
             end_job,
             floor(interval*24) hours,
             floor(interval*24*60) minutes,
             mod(interval*24*60*60,60) secs
      from (select DAT_FIN_TRT end_job,
                       DAT_DEBUT_TRT start_job,
                       DAT_FIN_TRT - DAT_DEBUT_TRT interval
                from coeadwh.tp_controle_batch where rownum < 15)
    

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    You could also just make a function like:
    Full article from ask tom: http://asktom.oracle.com/~tkyte/Misc/DateDiff.html
    Code:
    FUNCTION        "DATEDIFF" ( p_what in varchar2,
                                           p_d1   in date,
                                           p_d2   in date ) return number
      as
          l_result    number;
      begin
          select (p_d2-p_d1) *
                 decode( upper(p_what),
                         'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
           into l_result from dual;
    
          return l_result;
      end;
    
    13 rows selected.
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •