Solved

oracle date- finding elapsed time

Posted on 2012-03-19
8
420 Views
Last Modified: 2012-03-26
http://www.experts-exchange.com/Database/Oracle/Q_27540184.html

Earlier answered question.

select to_char(floor((sysdate -bleed_end_time)*24),'fm09') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003106'

by: sdstuberPosted on 2012-01-17 at 06:28:39ID: 37447220
----------------
Now my requirement is changed. Instead of 24 hours it has to be upto 120 hours.  Need help in this. Appreciate.
0
Comment
Question by:anumoses
  • 5
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 37739555
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003106'
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37739556
that will actually support up to 999 hours
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739593
TO_CHAR(FLOOR((SYSDATE-BLEED_END_TIME)*24),'FM009')||':'||TO_CHAR(ROUND(MOD((SYSDATE-BLEED_END_TIME)*24,1)*60),'FM09')

####:20

I dont think this is right.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:anumoses
ID: 37739619
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':'
|| to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003902'

---------------
120:24

I think we are in the right direction.
Thanks,
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739663
select to_char(floor((sysdate -bleed_end_time)*24),'fm009') || ':' || to_char(round(mod((sysdate -bleed_end_time)*24,1) * 60),'fm09')
from units@qlab  
 where unit_id = 'W039712003902'

120:33
 
select (sysdate - bleed_end_time)
from units@qlab  
where unit_id = 'W039712003902'  


5.02341435185185

      select to_char(trunc(sysdate)+ 5.02341435185185,'hh24:mi')
 from dual;

00:33

Here I need to get that 120:33
0
 
LVL 6

Author Comment

by:anumoses
ID: 37739764
I think I am good with the previous solution. Instead of converting further, I am diplaying the first value of 120:33 . Will make further tests and let you know. Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37740108
####:20  --- this means the result was 4 digits, i.e.  > 999 hours

if you need to display a results that large
change

this:   to_char(floor((sysdate -bleed_end_time)*24),'fm009')


to:   to_char(floor((sysdate -bleed_end_time)*24),'fm0009')

you can keep adding 0s to the format to expand as needed.



I'm not sure what you were trying to say in http:#a37739663
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 37768330
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question