Date Arithmetic

From: Nirmalya Das <nirmalya_at_hln.com>
Date: Fri, 14 Jul 2006 09:46:28 -0700
Message-ID: <20060714094628.modc0880484sc8s0@www.hln.com>

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*(sysdate-flush_stamp))/60)/60)/24)))+
       
(trunc((86400*(sysdate-flush_stamp))/60)-60*(trunc(((86400*(sysdate-flush_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*(sysdate-flush_stamp))/60)/60)/24)))+        

(trunc((86400*(sysdate-flush_stamp))/60)-60*(trunc(((86400*(sysdate-flush_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

Received on Fri Jul 14 2006 - 11:46:28 CDT