RE: Date Arithmetic

  • From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <nirmalya@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 14 Jul 2006 13:16:27 -0400

Nirmalya,

You can try this function.  It takes seconds as a parameter and displays
the results in a formatted string.


Function Second_Convert(in_sec in number)
           return varchar2 is

hrs number;
lmin number;
sec number;

ret_string varchar2(40);
--in_sec number := 3670;

begin

  hrs := trunc(in_sec / (60*60));
  lmin := trunc((in_sec - (hrs * 60*60))/60);
  sec := in_sec - ((hrs * 60 * 60) + (lmin * 60));
  ret_string := lpad(hrs,2,'0') || ':' || lpad(lmin,2,'0') || ':' ||
lpad(sec,2,'0');
--  dbms_output.put_line ('hhmiss=' || hrs || ':' || lmin || ':' ||
sec);
  return ret_string;

end;


So you could call it as:

Select second_convert((sysdate-flush_stamp)*3600*24) from dual

Feel free to change the function any way you wish.

Tom



--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.


-----Original Message-----

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nirmalya Das
Sent: Friday, July 14, 2006 12:46 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Date Arithmetic

Hi All,

I am trying to find the "number of minutes" between "sysdate" and
"flush_stamp".

I have the following query which works, but would like to find out if
anyone has
more elegant way of doing this using oracle functions like "EXTRACT" or
"INTERVAL" etc.

select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') "Now", 
to_char(flush_stamp,'mm/dd/yyyy hh24:mi:ss') as "Flush Time",
      
trunc((86400*(sysdate-flush_stamp))/60)-60*(trunc(((86400*(sysdate-flush
_stamp))/60)/60))
as "Mins",
      
trunc(((86400*(sysdate-flush_stamp))/60)/60)-24*(trunc((((86400*(sysdate
-flush_stamp))/60)/60)/24))
"Hrs",
       trunc((((86400*(sysdate-flush_stamp))/60)/60)/24) "Days",
       ((1440*trunc((((86400*(sysdate-flush_stamp))/60)/60)/24)))+
       
(60*trunc(((86400*(sysdate-flush_stamp))/60)/60)-24*(trunc((((86400*(sys
date-flush_stamp))/60)/60)/24)))+
       
(trunc((86400*(sysdate-flush_stamp))/60)-60*(trunc(((86400*(sysdate-flus
h_stamp))/60)/60))
       ) as "Tot Mins"
   from mega_owner_01.ia_ora_log
  where ((1440*trunc((((86400*(sysdate-flush_stamp))/60)/60)/24)))+
       
(60*trunc(((86400*(sysdate-flush_stamp))/60)/60)-24*(trunc((((86400*(sys
date-flush_stamp))/60)/60)/24)))+
       
(trunc((86400*(sysdate-flush_stamp))/60)-60*(trunc(((86400*(sysdate-flus
h_stamp))/60)/60)))
> 500

"Now"                   "Flush Time"            "Mins"  "Hrs"   "Days"
"Tot Mins"
"07/14/2006 09:43:27"   "07/14/2006 01:17:14"   "26"    "8"     "0"
"506"
"07/14/2006 09:43:27"   "07/14/2006 01:58:20"   "45"    "7"     "0"
"465"

TIA

Nirmalya
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l


Other related posts: