Date Arithmetic
- From: Nirmalya Das <nirmalya@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 14 Jul 2006 09:46:28 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
