oracle date- total elapsed time calculation

http://www.experts-exchange.com/Database/Oracle/Q_27543396.html
Already answered.

      :total_elapsed_time :=

select to_char(to_date('00:00:00','HH24:MI:SS')
 + (:filtration_end - :filtration_start), 'HH24:MI');                  
 
start 3/28/2012 9:13:52 AM
 
 end 3/28/2012 9:20:00 AM
Its giving me 00:06
but its seven minutes. Need help in fixing this
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
It's neither 6 minutes nor 7 minutes.

it's 6 minutes and 8 seconds.

however.  your format  HH24:MI  means you are only displaying the minutes and are dropping the seconds
sdstuberCommented:
change this portion...

:filtration_end - :filtration_start


to

trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
trunc(:filtration_end,'mi') - trunc(:filtration_start,'mi')

Will this give 6 minutes and 8 seconds.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
no

for one, even if the calculation did preserve the seconds, the HH24:MI format in the TO_CHAR didn't change, and that's why you don't see the seconds.

second, truncation removes the seconds so the math is at the minute level only

third - just try it,  always test for yourself.
slightwv (䄆 Netminder) Commented:
Do you want 6 minutes and 8 seconds?  In the other question you said you did not want seconds.

If you do just add seconds to the format:

select to_char(to_date('00:00:00','HH24:MI:SS')
 + (:filtration_end - :filtration_start), 'HH24:MI:SS');

If you do not want the seconds and want it rounded up then sdstuber's post will work.
anumosesAuthor Commented:
I want minutes and seconds
sdstuberCommented:
>>> I want minutes and seconds


then why did you ask for 7 minutes?


when you change the requirements like that -you completely invalidate previous effort and turn correct answers into wrong answers.
slightwv (䄆 Netminder) Commented:
>>I want minutes and seconds

That is not what you asked for initially...

anyway:  http:#a37777827
anumosesAuthor Commented:
Its my mistake. it was gicing 00:06 So I said wanted 7 minutes.
sdstuberCommented:
I suggest continuing with this question as originally asked
and then opening a new question to include the seconds.

that way the original posts, which apply to the original question will still be valid,  and slightwv can repost in the other question with his suggestion for the new requirement
anumosesAuthor Commented:
Will post another one for slightvw to answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.