RE: Date Arithmetic

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 14 Jul 2006 13:16:27 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF681406@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nirmalya Das Sent: Friday, July 14, 2006 12:46 PM
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 14 2006 - 12:16:27 CDT