Re: Date subtraction showing Day: Hour:Min:Sec format

From: Russ Seligman <rseligma_at_oracle.com>
Date: Thu, 6 Jan 1994 04:03:51 GMT
Message-ID: <RSELIGMA.94Jan5200352_at_seinfeld.oracle.com>

In article <1994Jan5.134049.21019_at_synapse.bms.com> kaz_at_solo.bms.com writes:
>
> This will print out the elapsed time between start_date and end_date
> as hh:mm:ss. For differences greater than 1 day, hours will be over
> 24 - you can play around with the first part to display days:hours if
> you prefer.
>
> select
> ltrim(to_char(trunc((end_date - start_date)*24),'09'))||':'||
> ltrim(to_char((end_date - start_date)*1440 -
> trunc((end_date - start_date)*24)*60,'09'))||':'||
> ltrim(to_char((end_date - start_date)*86400 -
> trunc((end_date - start_date)*1440)*60,'09'))
> from ...

Thanks, Joe, for this useful script, but it doesn't fully account for the rounding done by TO_CHAR. For example, at 30 seconds, the minutes calculation returns .5. You then try to TO_CHAR it using the '09' mask, but TO_CHAR rounds it up to '01'. Therefore, at 31 seconds, the string you select is '01:31'. This is fixed by TRUNCing the values before you TO_CHAR them:

   select
   ltrim(to_char(trunc((end_date - start_date)*24),'09'))||':'||    ltrim(to_char(trunc((end_date - start_date)*1440 -      trunc((end_date - start_date)*24)*60),'09'))||':'||    ltrim(to_char(trunc((end_date - start_date)*86400 -      trunc((end_date - start_date)*1440)*60),'09'))    from ...

I also converted this to PL/SQL for my own use, so I'll post that, as well:

function elapsed_time(start_time in date) return char is

   now       date;
   diff      number;
   hrs       number;
   mins      number;
   secs      number;

   elapsed varchar2(8);
begin

   now := sysdate;
   diff := now-start_time;

   hrs := diff*24;
   mins := diff*1440;
   secs := diff*86400;

   elapsed := ltrim(to_char(trunc(hrs),'09')) || ': '||
              ltrim(to_char(trunc(mins- trunc(hrs)*60),'09')) || ':' ||
              ltrim(to_char(trunc(secs - trunc(mins)*60),'09'));

   return(elapsed);
end; /* elapsed_time */

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Russ Seligman                 "If there's nothing wrong with me, maybe
Oracle Corporation             there's something wrong with the universe."
rseligma_at_oracle.com              --Beverly Crusher, ST:TNG

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- Received on Thu Jan 06 1994 - 05:03:51 CET